Combine data

fb250r · Apr 26th, 2002, 04:19 PM

Hello,

I came across your webpage and on the web and was wondering if you had any experiences with grouping data accordingly. I tried to use pivot tables but am unsuccessful.

Here is what I am trying to do.

I am trying to VLookup and Transpose data from the same source on the same row. All my data is given in columns but I would like to parse it out to Rows.

For Example here is my table. As you can see it is organized by columns. I would like to organize it so all the "FREQs" are on the same row but different columns.

Before:
Site Freq
York01 611
York01 650
York01 700
Lanc01 725
Lanc01 730
Lanc01 750

After:
Site Freq1 Freq 2 Freq 3
York01 611 650 700
Lanc01 725 730 750

Your help would be greatly appreciated!

LeeHitchen · Apr 26th, 2002, 04:30 PM

I've managed to get the result you require. Can you forward me your email address (mine is quoted on site) and I'll email you spreadshhet I have created.

Lee

fb250r · Apr 26th, 2002, 04:51 PM

Lee,

Did you get my email address?

LeeHitchen · Apr 26th, 2002, 04:53 PM

No. Infact, thinking about it's probably gone to my work address. Try this one: hitchbloke@lineone.net

Mark W. · Apr 26th, 2002, 05:59 PM

If A2:B7 contains...

{"York01",611
;"York01",650
;"York01",700
;"Lanc01",725
;"Lanc01",730
;"Lanc01",750}

...enter the formula, =(A2<>A3)+0, into C2, the formula,=IF(A2=A1,D1&","&B2,B2&""), into cell D2, and fill these down to the last data row (7:7).

Select column D and perform a Copy/Paste Special... Values.

Select column C, apply an AutoFilter for "equal 0", and delete the displayed rows.

After removing the AutoFilter delete columns B and C.

Finally, select the last (remaining) column, choose the Data | Text to Columns... menu command, specify comma delimited and press [Finish].

You now have...

{"York01",611,650,700
;"Lanc01",725,730,750}

[ This Message was edited by: Mark W. on 2002-04-26 13:01 ]

fb250r · Apr 26th, 2002, 06:35 PM

BINGO! Great idea Mark!

I hope one day I can pass on this little trick!

Thanks,

Frank

fb250r · Apr 29th, 2002, 01:24 PM

Jay,

Thank you as well! I took your little program and saved as a XLA. Works Great!

This Forum is by far the best!

Forever Grateful,

Frank

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 26th, 2002, 04:19 PM	#1
fb250r Join Date: Apr 2002 Posts: 104	Hello, I came across your webpage and on the web and was wondering if you had any experiences with grouping data accordingly. I tried to use pivot tables but am unsuccessful. Here is what I am trying to do. I am trying to VLookup and Transpose data from the same source on the same row. All my data is given in columns but I would like to parse it out to Rows. For Example here is my table. As you can see it is organized by columns. I would like to organize it so all the "FREQs" are on the same row but different columns. Before: Site Freq York01 611 York01 650 York01 700 Lanc01 725 Lanc01 730 Lanc01 750 After: Site Freq1 Freq 2 Freq 3 York01 611 650 700 Lanc01 725 730 750 Your help would be greatly appreciated!

Apr 26th, 2002, 04:30 PM	#2
LeeHitchen Join Date: Mar 2002 Location: England Posts: 26	I've managed to get the result you require. Can you forward me your email address (mine is quoted on site) and I'll email you spreadshhet I have created. Lee

Apr 26th, 2002, 04:51 PM	#3
fb250r Join Date: Apr 2002 Posts: 104	Lee, Did you get my email address?

Apr 26th, 2002, 04:53 PM	#4
LeeHitchen Join Date: Mar 2002 Location: England Posts: 26	No. Infact, thinking about it's probably gone to my work address. Try this one: hitchbloke@lineone.net

Apr 26th, 2002, 05:59 PM	#5
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,653	If A2:B7 contains... {"York01",611 ;"York01",650 ;"York01",700 ;"Lanc01",725 ;"Lanc01",730 ;"Lanc01",750} ...enter the formula, =(A2<>A3)+0, into C2, the formula,=IF(A2=A1,D1&","&B2,B2&""), into cell D2, and fill these down to the last data row (7:7). Select column D and perform a Copy/Paste Special... Values. Select column C, apply an AutoFilter for "equal 0", and delete the displayed rows. After removing the AutoFilter delete columns B and C. Finally, select the last (remaining) column, choose the Data \| Text to Columns... menu command, specify comma delimited and press [Finish]. You now have... {"York01",611,650,700 ;"Lanc01",725,730,750} [ This Message was edited by: Mark W. on 2002-04-26 13:01 ]

Apr 26th, 2002, 06:35 PM	#6
fb250r Join Date: Apr 2002 Posts: 104	BINGO! Great idea Mark! I hope one day I can pass on this little trick! Thanks, Frank

Apr 29th, 2002, 01:24 PM	#7
fb250r Join Date: Apr 2002 Posts: 104	Jay, Thank you as well! I took your little program and saved as a XLA. Works Great! This Forum is by far the best! Forever Grateful, Frank