CODEWITHSUNDEEP
excelvba
LikePod
LikePod

Reputation: 300

Match and move the columns in Excel

enter image description here

Hi;

I have a sheet which has 4 columns ;

Column A and C have product numbers

Column B and D are for detail of the products.

What i'm trying to do is ; If a number in column A match with a number in column C then i want to take the value (product details) of B to column E. but if the number repeats then at second row and match again with that number in column C then i want to that the new value to column F.

It's a really particular problem for us. We are trying to create a new sql database for our new web page and we have to convert all data like this.

I tried it with =vlookup formula in Excel but it didn't work. i think this problem can solve only with macros. (If i'm not wrong...)

PS_ Please check the image, it's even hard to explain in my mother language.

Thanks

Upvotes: 0

Views: 2003

Answers (2)

pnuts
pnuts

Reputation: 59460

If the order is not critical, a possible alternative with formulae only would be to append the second two columns to the first two (in a copy), add a blank Row1 and in ColumnC: 

 =IF(COLUMN()<=COUNTIF($A:$A,$A2)+2,INDIRECT("$B"&(COLUMN()-3+ROW())),"")

copied across and down to suit and in another column: 

  =A1=A2

copied down to suit. Hide ColumnB and filter the other column to select FALSE. Select Row2 to end and Paste Special, Values into F1 of original sheet.

Upvotes: 1

user4039065
user4039065

Reputation:

If you have less than 2000-3000 rows of data, this can be accomplished with some array formulas. If you have much more than that, a VBA solution would be better suited. These array formulas eat up calculation load exponentially as the ranges they refer to grow larger. At some point it simply isn't worth waiting for and a VBA solution would be better.

    Array formulas to aggregate and transpose

The array formula in F2 is,

=IF(LEN(F1), IFERROR(INDEX(A$1:A$999, MATCH(0, IF(LEN(A$1:A$999), COUNTIF(F$1:F1, A$1:A$999&""), 1), 0)), IFERROR(INDEX(C$1:C$999, MATCH(0, IF(LEN(C$1:C$999), COUNTIF(F$1:F1, C$1:C$999&""), 1), 0)), "")), "")

Note that this requires a header column label in F1. It cannot be put into F1. Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered correctly, fill down to pick up a unique list of all the part numbers from columns A and C.

The array formula in G2 is,

=IF(LEN($F2), IFERROR(INDEX($B$1:$B$999, MATCH(0, IF($A$1:$A$999=$F2, COUNTIF($F2:F2, $B$1:$B$999&""), 1), 0)), IFERROR(INDEX($D$1:$D$999, MATCH(0, IF($C$1:$C$999=$F2, COUNTIF($F2:F2, $D$1:$D$999&""), 1), 0)), "")), "")

This also requires finalizing with Ctrl+Shift+Enter↵. Once entered correctly, fill right several columns and then fill all of the formula down to match the entries retrieved in column F.

I've left those formulas to cover 999 rows. You may need to adjust those upwards if your needs exceed that range. A search for $999 and replace with $1999 can quickly accomplish that.

That is all there is to it. Make sure you leave a few rows at the bottom in case another file has more rows and a few columns to the right for teh same reason.

Upvotes: 1

Related Questions