Excel Help please. Group by columns? not sure of the correct

[Neilk]

Can someone help me convert the layout of an Excel spreadsheet? I think the example in the attached picture says it all. If I knew what the technical term for what I am trying to do, I would google it, but I don't. In a way it's like transposing, but I don't want to repeat the type column.




Plus the list I am looking at is 1000 rows long.

Thanks,
Neil

[legion]

You could use the transpose feature to do some of the work.

Me personally? I'd record a macro of myself doing it once, then alter the code to work down the spreadsheet until it encounters blank rows.

[jyl]

For each model (911, 914, etc), I would copy the part numbers (col B), paste-transpose that into a row, use that row to drive a VLOOKUP formula to create a table that extracts the info you want, in the format you want, and then copy-paste values that table to remove the formula and convert the table to hard-coded values. But I actually think the format you have is potentially more useful.

[MBAtarga]

I've just tried a method and it will get you close to your result wanted:
I'll explain the steps:
1 - insert a column before column B
2 - enter a formula in B2 - use the concatenate function to merge the part numbers and their descriptions =CONCATENATE("P/N ", C2, " DESC: ",D2)
3 - autofilter the column headings
4 - filter on 911
5 - highlight all entries in column B and COPY
6 - move to the new location where you want the data and in column A enter 911
7 - on that same row, column B, select Paste -Special, Values only, Transponse
repeat 5 through 7 for each "type" you have

[jyl]

That gets you concatenated part number and description, which isn't what OP wants.

[MBAtarga]

I did say it gets him "close"

[jyl]

Sent you a PM

[jyl]

OK, I flamed out, my way doesn't work because in the actual data, the values in each column are not unique, so VLOOKUP won't know which value to return.

I couldn't think of a way to do what Neil wants w/o using VB code. Can anyone else help?