Grouping/Transposing CSV File?

[glyn.devine]

Hi All, I've got a CSV file with two fields, product code and group. I
can get it into Excel just fine, what I want to do is convert the
groups into columns. Like, I'd want the "GOLF" column to have those 4
product codes beneath it.



002-0001-GOTIPPER GOLF
002-0002-GOTIPMOT GOLF
002-QUIC-GOLFTIPS GOLF
003-QUIC-GOLFGAME GOLF
004-QUIC-CASINOGA BUSINESS
005-0001-HOMEMAIN BUSINESS
005-QUIC-HOMEMAIN BUSINESS
006-QUIC-ROADSAFE SAFETY
007-QUIC-INDUSAFE SAFETY
008-QUIC-OFFISAFE SAFETY
009-QUIC-INTERNET BUSINESS
010-QUIC-BORGIDEA BUSINESS

Can someone help me out? I'm quite new to Excel.

 

[ShaneDevenshire]

Here are some steps to accomplish this task, you could code these. Suppose
the data is in cells A2:B13 with titles in cells A1:B1.

1. Select the Products (Golf...) and their title. Choose the command Data,
Filter, Advanced Filter. Choose Copy to another locations and pick a blank
cell as the Copy to range. Check Unique records only and click OK.

2. Select the output without the title and copy them. Select a blank cell
and choose Edit, Paste Special, Transpose. Suppose the first one is now in
F8 then enter the formula =IF($B2=F$8,$A2,0) in cell F9. Copy it down as
many rows as you have data, then copy it to the right for each group.

3. Select all the formulas and copy them, choose Edit, Paste Special, Values.

3b. Press Ctrl H and enter 0 in the Find what and leave the Replace with
empty, check Find Entire cell only, OK.

4. Press F5 and choose Special, Blanks, OK.

5. Press Ctrl+- (Ctrl Minus) and choose Shift cells up.