Converting Rows of Data to Columns

  • Thread starter Thread starter Greg Flowers
  • Start date Start date
G

Greg Flowers

I have the following data, for example.


Location # Vendor #
151 250368
151 312956
151 485648
103 251142
103 255442


My list has many, many locations. Anyone know an easy way to convert this
list so there is one line per location, with columns B-D (for example)
representing the vendor number. Here's an example of what I'm looking for.

Location B C D
151 250368 312956 485648
103 251142 255442



Thanks in Advance,

GF
 
Greg,

Assuming your table starts in A1 with the header row, and your data starts
in A2:B2:

Sort your data based on column A. Then in C2, enter the formula

=IF(AND($A2<>$A1,$A2=OFFSET($A2,COLUMN()-COLUMN($B2),0)),OFFSET($B2,COLUMN()-COLUMN($B2),0),IF($A2=$A1,"Delete",""))

(All on one line)

Copy it across the row for at least the same number of columns as the
maximum number of vendors for one location. Then copy those cells down to
match your data.

Then copy all the formulas, paste special values to remove the formulas,
then sort based on column C and delete any row with the word "Delete" in it.
Then you're done.

You can also do this with a macro, but this is just as easy, once you have
the formula.

HTH,
Bernie
MS Excel MVP
 
Bernie,
Thanks a million. That was perfect. Quick question though. Why the need
for the AND (it seems both criteria are the same)? Also, why the need for
the final IF statement, rather than just "Delete"?


Thanks again,

Greg
 
Greg,

It needs to find just the first occurence of each of the location codes that
have multiple occurences (thus, not equal to the one above, but equal to
those below) and differentiate between the first and the last. Also, it
needed to find any single occurences - thus the last check - otherwise, they
would also have "Delete".

HTH,
Bernie
MS Excel MVP
 
Back
Top