Combining similar data across multiple rows!

[comoke]

Good evening!

I am working on combining several rows of data with similar ID numbers. I have a small example below. For reference, my data set is every possible combination of an origin/destination for the contiguous US, so 2,401 rows total. Also, I will need to do this several times in the future with different ID numbers, so a general solution would be most helpful.

Essentially, I need to combine states with similar ID numbers into something more useful based on similarities in ID number and groups for State 2. For example, the ID numbers starting at 24 have a repeating MT, ND, WY in State 2. So I would like some sort of output like this:

AL, AR, AZ CA in one cell.
MT, ND, WY in another cell.

Since each of AL, AR, AZ, CA, each are matched with MT, ND, and WY and also with the same ID number.

Is this something that can be done? I appreciate any help you can offer.

Thank you!


ID State 1 State 2
15 NC NC
15 WI KS
16 FL FL
17 UT CO
18 NM NM
19 RI KS
20 CA AZ
21 UT ID
22 CA NV
23 MO KS
24 AL MT
24 AL ND
24 AL WY
24 AR MT
24 AR ND
24 AR WY
24 AZ MT
24 AZ ND
24 AZ WY
24 CA MT
24 CA ND
24 CA WY

 

[sandy666]

Re: Help with combining similar data across multiple rows!

you mean like this?

ID	State 1	State 2		ID	State 1	State 2
15​	NC	NC		15​	NC WI	NC KS
15​	WI	KS		16​	FL	FL
16​	FL	FL		17​	UT	CO
17​	UT	CO		18​	NM	NM
18​	NM	NM		19​	RI	KS
19​	RI	KS		20​	CA	AZ
20​	CA	AZ		21​	UT	ID
21​	UT	ID		22​	CA	NV
22​	CA	NV		23​	MO	KS
23​	MO	KS		24​	AL AL AL AR AR AR AZ AZ AZ CA CA CA	MT ND WY MT ND WY MT ND WY MT ND WY
24​	AL	MT
24​	AL	ND
24​	AL	WY
24​	AR	MT
24​	AR	ND
24​	AR	WY
24​	AZ	MT
24​	AZ	ND
24​	AZ	WY
24​	CA	MT
24​	CA	ND
24​	CA	WY

or

ID	State 1	State 2
15​	NC WI	NC KS
16​	FL	FL
17​	UT	CO
18​	NM	NM
19​	RI	KS
20​	CA	AZ
21​	UT	ID
22​	CA	NV
23​	MO	KS
24​	AL AR AZ CA	MT ND WY

 

[comoke]

Re: Help with combining similar data across multiple rows!

Good morning sandy,

I'm more looking for this:

15 NC - NC
15 WI - KS
16 FL - FL
17 UT - CO
18 NM - NM
19 RI - KS
20 CA - AZ
21 UT - ID
22 CA - NV
23 MO - KS
24 AL AR AZ CA - MT ND WY

I need to enter it as a From State 1 To State 2 situation, so only the ones where they have the state combos in common, e.g. each of AL AR AZ CA are going to each of MT ND WY. But for 15 since only NC is going to NC and only WI is going to KS, they need to be separate.

Thanks for the question, I apologize if I didn't explain it well enough!

 

[Nasmin Saheed]

Re: Help with combining similar data across multiple rows!

you mean like this?

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ID[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]State 1[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]State 2[/COLOR]		[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ID[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]State 1[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]State 2[/COLOR]
15​	NC	NC		15​	NC WI	NC KS
15​	WI	KS		16​	FL	FL
16​	FL	FL		17​	UT	CO
17​	UT	CO		18​	NM	NM
18​	NM	NM		19​	RI	KS
19​	RI	KS		20​	CA	AZ
20​	CA	AZ		21​	UT	ID
21​	UT	ID		22​	CA	NV
22​	CA	NV		23​	MO	KS
23​	MO	KS		24​	AL AL AL AR AR AR AZ AZ AZ CA CA CA	MT ND WY MT ND WY MT ND WY MT ND WY
24​	AL	MT
24​	AL	ND
24​	AL	WY
24​	AR	MT
24​	AR	ND
24​	AR	WY
24​	AZ	MT
24​	AZ	ND
24​	AZ	WY
24​	CA	MT
24​	CA	ND
24​	CA	WY

<tbody>
</tbody>

or

[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ID[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]State 1[/COLOR]	[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]State 2[/COLOR]
15​	NC WI	NC KS
16​	FL	FL
17​	UT	CO
18​	NM	NM
19​	RI	KS
20​	CA	AZ
21​	UT	ID
22​	CA	NV
23​	MO	KS
24​	AL AR AZ CA	MT ND WY

<tbody>
</tbody>

Hi Sandy,

How do you include this Chat - i have tried many time to include like this cart with multiple colour how you have uploaded colour with Green & Blue.

Thanks - Nasmin

 

[sandy666]

Re: Help with combining similar data across multiple rows!

how you have uploaded colour with Green & Blue.

because my source table/s or ranges (whatever) has colours

 

[sandy666]

Re: Help with combining similar data across multiple rows!

Hope this is what you want

ID	State 1	State 2		ID	State 1	State 2
15​	NC	NC		15​	NC	NC
15​	WI	KS		15​	WI	KS
16​	FL	FL		16​	FL	FL
17​	UT	CO		17​	UT	CO
18​	NM	NM		18​	NM	NM
19​	RI	KS		19​	RI	KS
20​	CA	AZ		20​	CA	AZ
21​	UT	ID		21​	UT	ID
22​	CA	NV		22​	CA	NV
23​	MO	KS		23​	MO	KS
24​	AL	MT		24​	AL AR AZ CA	MT ND WY
24​	AL	ND
24​	AL	WY
24​	AR	MT
24​	AR	ND
24​	AR	WY
24​	AZ	MT
24​	AZ	ND
24​	AZ	WY
24​	CA	MT
24​	CA	ND
24​	CA	WY

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group1 = Table.Group(Source, {"ID", "State 2"}, {{"Count", each _, type table}}),
    List1 = Table.AddColumn(Group1, "State 1", each List.Distinct(Table.Column([Count],"State 1"))),
    Extract1 = Table.TransformColumns(List1, {"State 1", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    RC1 = Table.RemoveColumns(Extract1,{"Count"}),
    Group2 = Table.Group(RC1, {"ID", "State 1"}, {{"Count", each _, type table}}),
    List2 = Table.AddColumn(Group2, "State 2", each List.Distinct(Table.Column([Count],"State 2"))),
    Extract2 = Table.TransformColumns(List2, {"State 2", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    RC2 = Table.RemoveColumns(Extract2,{"Count"})
in
    RC2[/SIZE]

Edit:
this is PowerQuery M-code, not any vba