Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping/Transposing CSV File?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping/Transposing CSV File?
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. -- Cheers, Shane Devenshire Microsoft Excel MVP " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transposing | Excel Discussion (Misc queries) | |||
Transposing | Excel Worksheet Functions | |||
transposing question... | Excel Worksheet Functions | |||
TRANSPOSING | Excel Discussion (Misc queries) | |||
Transposing cells | Excel Discussion (Misc queries) |