Move cells into catergories

[steffen82]

Hi

Someone's help would be very appreciated. I have a list of addresses where there are multiple street names and all of them have a different house number in the cell to the left of street name. I would like all of the house numbers to come under the street name. I have removed duplicates of street names and transposed them from column to top row.

Below is an example of what I would like to happen (with a formula a bit like a vlookup or an array)

[TABLE="width: 448"]
<tbody>[TR]
[TD="colspan: 2"]How Data Looks[/TD]
[TD][/TD]
[TD="colspan: 3"]How would like data to look[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]House 1[/TD]
[TD]Street 1[/TD]
[TD][/TD]
[TD]Street 1[/TD]
[TD]Street 2[/TD]
[TD]Street 3[/TD]
[TD]Street 4[/TD]
[/TR]
[TR]
[TD]House 2[/TD]
[TD]Street 1[/TD]
[TD][/TD]
[TD]House 1[/TD]
[TD]House 1[/TD]
[TD]House 1[/TD]
[TD]House 1[/TD]
[/TR]
[TR]
[TD]House 3[/TD]
[TD]Street 1[/TD]
[TD][/TD]
[TD]House 2[/TD]
[TD]House 2[/TD]
[TD]House 2[/TD]
[TD]House 2[/TD]
[/TR]
[TR]
[TD]House 4[/TD]
[TD]Street 1[/TD]
[TD][/TD]
[TD]House 3[/TD]
[TD]House 3[/TD]
[TD]House 3[/TD]
[TD]House 3[/TD]
[/TR]
[TR]
[TD]House 1[/TD]
[TD]Street 2[/TD]
[TD][/TD]
[TD]House 4[/TD]
[TD]House 4[/TD]
[TD]House 4[/TD]
[TD]House 4[/TD]
[/TR]
[TR]
[TD]House 2[/TD]
[TD]Street 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]House 5[/TD]
[TD]House 5[/TD]
[/TR]
[TR]
[TD]House 3[/TD]
[TD]Street 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 4[/TD]
[TD]Street 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 1[/TD]
[TD]Street 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 2[/TD]
[TD]Street 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 3[/TD]
[TD]Street 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 4[/TD]
[TD]Street 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 5[/TD]
[TD]Street 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 1[/TD]
[TD]Street 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 2[/TD]
[TD]Street 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 3[/TD]
[TD]Street 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 4[/TD]
[TD]Street 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House 5[/TD]
[TD]Street 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="7"></colgroup>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

I have done this on a single sheet. Hopefully you can adapt to get the results on to a second sheet if it does what you want.

Formula in H2 is copied across and down.

Excel Workbook
	E	F	G	H	I	J	K
1	House 1	Street 1		Street 1	Street 2	Street 3	Street 4
2	House 2	Street 1		House 1	House 1	House 1	House 1
3	House 3	Street 1		House 2	House 2	House 2	House 2
4	House 4	Street 1		House 3	House 3	House 3	House 3
5	House 1	Street 2		House 4	House 4	House 4	House 4
6	House 2	Street 2				House 5	House 5
7	House 3	Street 2
8	House 4	Street 2
9	House 1	Street 3
10	House 2	Street 3
11	House 3	Street 3
12	House 4	Street 3
13	House 5	Street 3
14	House 1	Street 4
15	House 2	Street 4
16	House 3	Street 4
17	House 4	Street 4
18	House 5	Street 4
19
Rearrange

 

[steffen82]

Wow don't understand it but it seems to work - what a genius thank you very much.

Steff

 

[Peter_SSs]

Wow don't understand it but it seems to work - what a genius thank you very much.

Steff

You are very welcome.