Need Help with Match/IF/ISNUMBER problem.

MikeRob

New Member
Joined
May 8, 2020
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
I have a problem I'm trying to solve. hopefully I explain this right. I'm trying do find a formula to insert 3 missing columns of data from another data set that has those columns. the data with the missing columns has over 600 rows. and duplicate values.. the data that has the columns i need have no duplicates. I need to get the columns form the non duplicate value dat to match up correctly with but fill in all the duplicate value data. I'm sorry if I have anyone confused so i provided an example that shows what I'm trying to achieve.

the dat set that has the missing columns but contains duplicates is Column A - C

the data that doesn't have duplicates but has the 3 missing columns I need (columns F, I, and J) is in Columns E- J

the 3rd set of data Columns L - Q is the result I'm trying to achieve.

MuniWardDistrictMunicipalityMuni CodeWardDistrictRegionLine Up No.MunicipalityMuni CodeWardDistrictRegionLine Up No.
ALEPPO0001ALEPPO1010001
4​
1​
ALEPPO1010001
4​
1​
ALEPPO0001ASPINWALL1020001
3​
1​
ALEPPO1010001
4​
1​
ALEPPO0001ASPINWALL1020002
3​
2​
ALEPPO1010001
4​
1​
ALEPPO0001ASPINWALL1020003
3​
3​
ALEPPO1010001
4​
1​
ALEPPO0001AVALON1030100
4​
2​
ALEPPO1010001
4​
1​
ASPINWALL0001AVALON1030201
4​
3​
ASPINWALL1020001
3​
1​
ASPINWALL0001AVALON1030202
4​
4​
ASPINWALL1020001
3​
1​
ASPINWALL0001AVALON1030301
4​
5​
ASPINWALL1020001
3​
1​
ASPINWALL0001AVALON1030302
4​
6​
ASPINWALL1020001
3​
1​
ASPINWALL0001AVALON1030303
4​
7​
ASPINWALL1020001
3​
1​
ASPINWALL0002BALDWIN BORO1040001
1​
1​
ASPINWALL1020002
3​
2​
ASPINWALL0002BALDWIN BORO1040002
1​
2​
ASPINWALL1020002
3​
2​
ASPINWALL0002BALDWIN BORO1040003
1​
3​
ASPINWALL1020002
3​
2​
ASPINWALL0002BALDWIN BORO1040004
1​
4​
ASPINWALL1020002
3​
2​
ASPINWALL0002BALDWIN BORO1040005
1​
5​
ASPINWALL1020002
3​
2​
ASPINWALL0003BALDWIN BORO1040006
1​
6​
ASPINWALL1020003
3​
3​
ASPINWALL0003BALDWIN BORO1040007
1​
7​
ASPINWALL1020003
3​
3​
ASPINWALL0003BALDWIN BORO1040008
1​
8​
ASPINWALL1020003
3​
3​
ASPINWALL0003BALDWIN BORO1040009
1​
9​
ASPINWALL1020003
3​
3​
ASPINWALL0003BALDWIN BORO1040010
1​
10​
ASPINWALL1020003
3​
3​
AVALON0100BALDWIN BORO1040011
1​
11​
AVALON1030100
4​
2​
AVALON0100BALDWIN BORO1040012
1​
12​
AVALON1030100
4​
2​
AVALON0100BALDWIN BORO1040013
1​
13​
AVALON1030100
4​
2​
AVALON0100BALDWIN BORO1040014
1​
14​
AVALON1030100
4​
2​
AVALON0100BALDWIN BORO1040015
1​
15​
AVALON1030100
4​
2​
AVALON0201BALDWIN BORO1040016
1​
16​
AVALON1030201
4​
3​
AVALON0201BALDWIN BORO1040017
1​
17​
AVALON1030201
4​
3​
AVALON0201BALDWIN BORO1040018
1​
18​
AVALON1030201
4​
3​
AVALON0201BALDWIN TWP1050001
5​
1​
AVALON1030201
4​
3​
AVALON0201BALDWIN TWP1050002
5​
2​
AVALON1030201
4​
3​
AVALON0201BELL ACRES1060001
4​
8​
AVALON1030201
4​
3​
AVALON0202BELLEVUE1070101
4​
9​
AVALON1030202
4​
4​
AVALON0202BELLEVUE1070102
4​
10​
AVALON1030202
4​
4​
AVALON0202BELLEVUE1070201
4​
11​
AVALON1030202
4​
4​
AVALON0202BELLEVUE1070202
4​
12​
AVALON1030202
4​
4​
AVALON0202BELLEVUE1070301
4​
13​
AVALON1030202
4​
4​
AVALON0202BELLEVUE1070302
4​
14​
AVALON1030202
4​
4​
AVALON0301BEN AVON1080001
4​
15​
AVALON1030301
4​
5​
AVALON0301BEN AVON1080002
4​
16​
AVALON1030301
4​
5​
AVALON0301BEN AVON HGTS1090001
4​
17​
AVALON1030301
4​
5​
AVALON0301BETHEL PARK1100101
5​
3​
AVALON1030301
4​
5​
AVALON0301BETHEL PARK1100102
5​
4​
AVALON1030301
4​
5​
AVALON0302BETHEL PARK1100103
5​
5​
AVALON1030302
4​
6​
AVALON0302BETHEL PARK1100201
5​
6​
AVALON1030302
4​
6​
AVALON0302BETHEL PARK1100202
5​
7​
AVALON1030302
4​
6​
AVALON0302BETHEL PARK1100203
5​
8​
AVALON1030302
4​
6​
AVALON0303BETHEL PARK1100301
5​
9​
AVALON1030303
4​
7​
AVALON0303BETHEL PARK1100302
5​
10​
AVALON1030303
4​
7​
AVALON0303BETHEL PARK1100303
5​
11​
AVALON1030303
4​
7​
AVALON0303BETHEL PARK1100401
5​
12​
AVALON1030303
4​
7​

I may not have the right formula types like I described in the thread title.so if that's the case help is definitely needed. Thank you in advance for your help.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
maybe Power Query (Get&Transform)
MunicipalityMuni CodeWardDistrictRegionLine Up No.
ALEPPO1010141
ALEPPO1010141
ALEPPO1010141
ALEPPO1010141
ALEPPO1010141
ASPINWALL1020131
ASPINWALL1020131
ASPINWALL1020131
ASPINWALL1020131
ASPINWALL1020131
ASPINWALL1020232
ASPINWALL1020232
ASPINWALL1020232
ASPINWALL1020232
ASPINWALL1020232
ASPINWALL1020333
ASPINWALL1020333
ASPINWALL1020333
ASPINWALL1020333
ASPINWALL1020333
AVALON1031042
AVALON1031042
AVALON1031042
AVALON1031042
AVALON1031042
AVALON1032143
AVALON1032143
AVALON1032143
AVALON1032143
AVALON1032143
AVALON1032143
AVALON1032244
AVALON1032244
AVALON1032244
AVALON1032244
AVALON1032244
AVALON1032244
AVALON1033145
AVALON1033145
AVALON1033145
AVALON1033145
AVALON1033145
AVALON1033246
AVALON1033246
AVALON1033246
AVALON1033246
AVALON1033347
AVALON1033347
AVALON1033347
AVALON1033347

Power Query:
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Join = Table.NestedJoin(Source1,{"Muni", "Ward", "District"},Source2,{"Municipality", "Ward", "District"},"Table",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"Municipality", "Muni Code", "Ward", "District", "Region", "Line Up No."}, {"Municipality", "Muni Code", "Ward.1", "District.1", "Region", "Line Up No."}),
    TSC = Table.SelectColumns(Expand,{"Municipality", "Muni Code", "Ward.1", "District.1", "Region", "Line Up No."}),
    Ren = Table.RenameColumns(TSC,{{"Ward.1", "Ward"}, {"District.1", "District"}})
in
    Ren
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
or shorter a bit
Power Query:
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    Join = Table.NestedJoin(Source1,{"Muni", "Ward", "District"},Source2,{"Municipality", "Ward", "District"},"Table",JoinKind.LeftOuter),
    TSC = Table.SelectColumns(Join,{"Table"}),
    Expand = Table.ExpandTableColumn(TSC, "Table", {"Municipality", "Muni Code", "Ward", "District", "Region", "Line Up No."}, {"Municipality", "Muni Code", "Ward", "District", "Region", "Line Up No."})
in
    Expand
 

MikeRob

New Member
Joined
May 8, 2020
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
or shorter a bit
Power Query:
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    Join = Table.NestedJoin(Source1,{"Muni", "Ward", "District"},Source2,{"Municipality", "Ward", "District"},"Table",JoinKind.LeftOuter),
    TSC = Table.SelectColumns(Join,{"Table"}),
    Expand = Table.ExpandTableColumn(TSC, "Table", {"Municipality", "Muni Code", "Ward", "District", "Region", "Line Up No."}, {"Municipality", "Muni Code", "Ward", "District", "Region", "Line Up No."})
in
    Expand
Okay thank you so much... I want to try to apply this but I’m just learning excel this year and haven’t gotten to power query yet. Is there any way you can send any type of instruction on how to implement this? Or is it a copy and paste thing and how I would do that? I can clearly see it works but just need to know how to apply it please. Again thank you wry much for your help... it’s spot on!!
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
Copy code from the post#3 then
  1. Data tab
  2. New Query
  3. From Other Sources
  4. Blank Query
it will open PQ Editor, go to Advanced Editor and replace all there with copied code, OK then Close&Load
note: be sure your source tables have the same names as in the code or change them suitably

part of the source tables
Table1Table2
MuniWardDistrictMunicipalityMuni CodeWardDistrictRegionLine Up No.
ALEPPO01ALEPPO1010141
ALEPPO01ASPINWALL1020131
ALEPPO01ASPINWALL1020232
ALEPPO01ASPINWALL1020333
ALEPPO01AVALON1031042
ASPINWALL01AVALON1032143
ASPINWALL01AVALON1032244
ASPINWALL01AVALON1033145
ASPINWALL01AVALON1033246
ASPINWALL01AVALON1033347
ASPINWALL02BALDWIN BORO1040111

if you want to know more about Power Query:
 

Forum statistics

Threads
1,147,750
Messages
5,742,981
Members
423,769
Latest member
LongToast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top