Combining similar data across multiple rows!

comoke

New Member
Joined
Jun 20, 2019
Messages
2
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,406
Re: Help with combining similar data across multiple rows!

you mean like this?

IDState 1State 2IDState 1State 2
15​
NCNC
15​
NC WINC KS
15​
WIKS
16​
FLFL
16​
FLFL
17​
UTCO
17​
UTCO
18​
NMNM
18​
NMNM
19​
RIKS
19​
RIKS
20​
CAAZ
20​
CAAZ
21​
UTID
21​
UTID
22​
CANV
22​
CANV
23​
MOKS
23​
MOKS
24​
AL AL AL AR AR AR AZ AZ AZ CA CA CAMT ND WY MT ND WY MT ND WY MT ND WY
24​
ALMT
24​
ALND
24​
ALWY
24​
ARMT
24​
ARND
24​
ARWY
24​
AZMT
24​
AZND
24​
AZWY
24​
CAMT
24​
CAND
24​
CAWY

or

IDState 1State 2
15​
NC WINC KS
16​
FLFL
17​
UTCO
18​
NMNM
19​
RIKS
20​
CAAZ
21​
UTID
22​
CANV
23​
MOKS
24​
AL AR AZ CAMT ND WY
 
Last edited:

comoke

New Member
Joined
Jun 20, 2019
Messages
2
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

New Member
Joined
Jun 11, 2015
Messages
42
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​
NCNC
15​
NC WINC KS
15​
WIKS
16​
FLFL
16​
FLFL
17​
UTCO
17​
UTCO
18​
NMNM
18​
NMNM
19​
RIKS
19​
RIKS
20​
CAAZ
20​
CAAZ
21​
UTID
21​
UTID
22​
CANV
22​
CANV
23​
MOKS
23​
MOKS
24​
AL AL AL AR AR AR AZ AZ AZ CA CA CAMT ND WY MT ND WY MT ND WY MT ND WY
24​
ALMT
24​
ALND
24​
ALWY
24​
ARMT
24​
ARND
24​
ARWY
24​
AZMT
24​
AZND
24​
AZWY
24​
CAMT
24​
CAND
24​
CAWY

<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 WINC KS
16​
FLFL
17​
UTCO
18​
NMNM
19​
RIKS
20​
CAAZ
21​
UTID
22​
CANV
23​
MOKS
24​
AL AR AZ CAMT 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
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,406
Re: Help with combining similar data across multiple rows!

Hope this is what you want

IDState 1State 2IDState 1State 2
15​
NCNC
15​
NCNC
15​
WIKS
15​
WIKS
16​
FLFL
16​
FLFL
17​
UTCO
17​
UTCO
18​
NMNM
18​
NMNM
19​
RIKS
19​
RIKS
20​
CAAZ
20​
CAAZ
21​
UTID
21​
UTID
22​
CANV
22​
CANV
23​
MOKS
23​
MOKS
24​
ALMT
24​
AL AR AZ CAMT ND WY
24​
ALND
24​
ALWY
24​
ARMT
24​
ARND
24​
ARWY
24​
AZMT
24​
AZND
24​
AZWY
24​
CAMT
24​
CAND
24​
CAWY

Code:
[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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,574
Messages
5,469,475
Members
406,655
Latest member
pwilson06

This Week's Hot Topics

Top