Multiple rows in multiple columns

Closed
Jay - Nov 16, 2011 at 03:14 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 16, 2011 at 10:55 PM
Hello,

I have a spreadsheet with two columns and 100+ rows.
Data in column A, the category repeats, Data in column B is unique to what is in column A.
example.
Column A
Color1
Color1

Color2
Color2
Color2

Color3
Color3
Color3
Color3

Column B
Red
Green

Gray
Green
Red

Yellow
Black
Red
Blue

What I would like to do is take the fields in column B and insert those values in to columns. The result would look like this:
Column A
Color
Column C
Red
Column D
Green

Column A
Color2
Column C
Gray
Column D
Green
Column E
Red

Column A
Color 3
Column C
Yeallow...

etc.

I tried doing a pivot table but didn't work correctly, someone mentioned I might be able to make it work with a macros but I am not familiar with macros in excel.
Any help will be greatly appreciated.
Thanks


1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 16, 2011 at 10:55 PM
1. the entry in column A in each sub group should be exactly same for color1 (no spelling mistake or unnecssry spces. better copy the first entry with other cells in the sub group
2. I have made provison for 100 rows.
3.see the file which you can download from this web page and see the formula i C1

http://speedy.sh/KybZ4/jay.xls

4. copy C1 to D1 and C4:e4 and C8:F8
0