Grouping data and pasting values across columns

transpose_image.PNG

Hey Guys,

I have attached an image above for better understanding.

I have about few thousands of rows of UPC along with one/multiple associated IDs . My desired result should have unique UPC in one column, and associated ID's across columns.

Macro? Formula? Anything that could make it work?

Thanks,

Jaz

Insert a new column C (moving D over to E)
In C3 = =A3&"_" & COUNTIF($A$3:A3,A3) dragged down

Paste a unique list of UPC in Col E (ask if you don't know how to do this)

In F3
=IF(COUNTIF($A$3:$A$10000,$E3)<COLUMN(A1),"", INDEX($B$3:$B$10000,MATCH($E3&"_"& COLUMN(A1), $C$3:$C$10000,0)))
Drag across and down.

Does that work for you?

The first equation just allows us to differentiate between multiple values of the same UPC by adding _1, _2, etc

Formula:

Please Login or Register  to view this content.

The first part of the second formula

Formula:

Please Login or Register  to view this content.

will put a blank into a cell if the # of times a value appears is less than the column we've dragged the formula over. For example, if we dragged that formula 2 columns to the right, it would become

Formula:

Please Login or Register  to view this content.

COLUMN(C1) simplifies to 3. So if the count of the value in E3 is less than 3, Excel returns a blank ("")

Formula:

Please Login or Register  to view this content.

INDEX(Range, Relative Row to return)
The Range is B3:B10000. Let's say we're looking for 123456789
=MATCH(123456789_1, C3:C100000,0) (The zero means we want to return exact matches only). That will return a row and Excel will return the value in Col B in the same row (counting from B3). Clear as mud?