Need Help with Match/IF/ISNUMBER problem.

MikeRob · Oct 30, 2020

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.

Muni	Ward	District	Municipality	Muni Code	Ward	District	Region	Line Up No.	Municipality	Muni Code	Ward	District	Region	Line Up No.
ALEPPO	00	01	ALEPPO	101	00	01	4	1	ALEPPO	101	00	01	4	1
ALEPPO	00	01	ASPINWALL	102	00	01	3	1	ALEPPO	101	00	01	4	1
ALEPPO	00	01	ASPINWALL	102	00	02	3	2	ALEPPO	101	00	01	4	1
ALEPPO	00	01	ASPINWALL	102	00	03	3	3	ALEPPO	101	00	01	4	1
ALEPPO	00	01	AVALON	103	01	00	4	2	ALEPPO	101	00	01	4	1
ASPINWALL	00	01	AVALON	103	02	01	4	3	ASPINWALL	102	00	01	3	1
ASPINWALL	00	01	AVALON	103	02	02	4	4	ASPINWALL	102	00	01	3	1
ASPINWALL	00	01	AVALON	103	03	01	4	5	ASPINWALL	102	00	01	3	1
ASPINWALL	00	01	AVALON	103	03	02	4	6	ASPINWALL	102	00	01	3	1
ASPINWALL	00	01	AVALON	103	03	03	4	7	ASPINWALL	102	00	01	3	1
ASPINWALL	00	02	BALDWIN BORO	104	00	01	1	1	ASPINWALL	102	00	02	3	2
ASPINWALL	00	02	BALDWIN BORO	104	00	02	1	2	ASPINWALL	102	00	02	3	2
ASPINWALL	00	02	BALDWIN BORO	104	00	03	1	3	ASPINWALL	102	00	02	3	2
ASPINWALL	00	02	BALDWIN BORO	104	00	04	1	4	ASPINWALL	102	00	02	3	2
ASPINWALL	00	02	BALDWIN BORO	104	00	05	1	5	ASPINWALL	102	00	02	3	2
ASPINWALL	00	03	BALDWIN BORO	104	00	06	1	6	ASPINWALL	102	00	03	3	3
ASPINWALL	00	03	BALDWIN BORO	104	00	07	1	7	ASPINWALL	102	00	03	3	3
ASPINWALL	00	03	BALDWIN BORO	104	00	08	1	8	ASPINWALL	102	00	03	3	3
ASPINWALL	00	03	BALDWIN BORO	104	00	09	1	9	ASPINWALL	102	00	03	3	3
ASPINWALL	00	03	BALDWIN BORO	104	00	10	1	10	ASPINWALL	102	00	03	3	3
AVALON	01	00	BALDWIN BORO	104	00	11	1	11	AVALON	103	01	00	4	2
AVALON	01	00	BALDWIN BORO	104	00	12	1	12	AVALON	103	01	00	4	2
AVALON	01	00	BALDWIN BORO	104	00	13	1	13	AVALON	103	01	00	4	2
AVALON	01	00	BALDWIN BORO	104	00	14	1	14	AVALON	103	01	00	4	2
AVALON	01	00	BALDWIN BORO	104	00	15	1	15	AVALON	103	01	00	4	2
AVALON	02	01	BALDWIN BORO	104	00	16	1	16	AVALON	103	02	01	4	3
AVALON	02	01	BALDWIN BORO	104	00	17	1	17	AVALON	103	02	01	4	3
AVALON	02	01	BALDWIN BORO	104	00	18	1	18	AVALON	103	02	01	4	3
AVALON	02	01	BALDWIN TWP	105	00	01	5	1	AVALON	103	02	01	4	3
AVALON	02	01	BALDWIN TWP	105	00	02	5	2	AVALON	103	02	01	4	3
AVALON	02	01	BELL ACRES	106	00	01	4	8	AVALON	103	02	01	4	3
AVALON	02	02	BELLEVUE	107	01	01	4	9	AVALON	103	02	02	4	4
AVALON	02	02	BELLEVUE	107	01	02	4	10	AVALON	103	02	02	4	4
AVALON	02	02	BELLEVUE	107	02	01	4	11	AVALON	103	02	02	4	4
AVALON	02	02	BELLEVUE	107	02	02	4	12	AVALON	103	02	02	4	4
AVALON	02	02	BELLEVUE	107	03	01	4	13	AVALON	103	02	02	4	4
AVALON	02	02	BELLEVUE	107	03	02	4	14	AVALON	103	02	02	4	4
AVALON	03	01	BEN AVON	108	00	01	4	15	AVALON	103	03	01	4	5
AVALON	03	01	BEN AVON	108	00	02	4	16	AVALON	103	03	01	4	5
AVALON	03	01	BEN AVON HGTS	109	00	01	4	17	AVALON	103	03	01	4	5
AVALON	03	01	BETHEL PARK	110	01	01	5	3	AVALON	103	03	01	4	5
AVALON	03	01	BETHEL PARK	110	01	02	5	4	AVALON	103	03	01	4	5
AVALON	03	02	BETHEL PARK	110	01	03	5	5	AVALON	103	03	02	4	6
AVALON	03	02	BETHEL PARK	110	02	01	5	6	AVALON	103	03	02	4	6
AVALON	03	02	BETHEL PARK	110	02	02	5	7	AVALON	103	03	02	4	6
AVALON	03	02	BETHEL PARK	110	02	03	5	8	AVALON	103	03	02	4	6
AVALON	03	03	BETHEL PARK	110	03	01	5	9	AVALON	103	03	03	4	7
AVALON	03	03	BETHEL PARK	110	03	02	5	10	AVALON	103	03	03	4	7
AVALON	03	03	BETHEL PARK	110	03	03	5	11	AVALON	103	03	03	4	7
AVALON	03	03	BETHEL PARK	110	04	01	5	12	AVALON	103	03	03	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.

sandy666 · Oct 30, 2020

maybe Power Query (Get&Transform)



Municipality	Muni Code	Ward	District	Region	Line Up No.
ALEPPO	101	0	1	4	1
ALEPPO	101	0	1	4	1
ALEPPO	101	0	1	4	1
ALEPPO	101	0	1	4	1
ALEPPO	101	0	1	4	1
ASPINWALL	102	0	1	3	1
ASPINWALL	102	0	1	3	1
ASPINWALL	102	0	1	3	1
ASPINWALL	102	0	1	3	1
ASPINWALL	102	0	1	3	1
ASPINWALL	102	0	2	3	2
ASPINWALL	102	0	2	3	2
ASPINWALL	102	0	2	3	2
ASPINWALL	102	0	2	3	2
ASPINWALL	102	0	2	3	2
ASPINWALL	102	0	3	3	3
ASPINWALL	102	0	3	3	3
ASPINWALL	102	0	3	3	3
ASPINWALL	102	0	3	3	3
ASPINWALL	102	0	3	3	3
AVALON	103	1	0	4	2
AVALON	103	1	0	4	2
AVALON	103	1	0	4	2
AVALON	103	1	0	4	2
AVALON	103	1	0	4	2
AVALON	103	2	1	4	3
AVALON	103	2	1	4	3
AVALON	103	2	1	4	3
AVALON	103	2	1	4	3
AVALON	103	2	1	4	3
AVALON	103	2	1	4	3
AVALON	103	2	2	4	4
AVALON	103	2	2	4	4
AVALON	103	2	2	4	4
AVALON	103	2	2	4	4
AVALON	103	2	2	4	4
AVALON	103	2	2	4	4
AVALON	103	3	1	4	5
AVALON	103	3	1	4	5
AVALON	103	3	1	4	5
AVALON	103	3	1	4	5
AVALON	103	3	1	4	5
AVALON	103	3	2	4	6
AVALON	103	3	2	4	6
AVALON	103	3	2	4	6
AVALON	103	3	2	4	6
AVALON	103	3	3	4	7
AVALON	103	3	3	4	7
AVALON	103	3	3	4	7
AVALON	103	3	3	4	7

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 · Oct 30, 2020

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 · Nov 2, 2020

sandy666 said:

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 · Nov 2, 2020

Copy code from the post#3 then

Data tab
New Query
From Other Sources
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



Table1			Table2
Muni	Ward	District	Municipality	Muni Code	Ward	District	Region	Line Up No.
ALEPPO	0	1	ALEPPO	101	0	1	4	1
ALEPPO	0	1	ASPINWALL	102	0	1	3	1
ALEPPO	0	1	ASPINWALL	102	0	2	3	2
ALEPPO	0	1	ASPINWALL	102	0	3	3	3
ALEPPO	0	1	AVALON	103	1	0	4	2
ASPINWALL	0	1	AVALON	103	2	1	4	3
ASPINWALL	0	1	AVALON	103	2	2	4	4
ASPINWALL	0	1	AVALON	103	3	1	4	5
ASPINWALL	0	1	AVALON	103	3	2	4	6
ASPINWALL	0	1	AVALON	103	3	3	4	7
ASPINWALL	0	2	BALDWIN BORO	104	0	1	1	1

if you want to know more about Power Query:

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

Need Help with Match/IF/ISNUMBER problem.

MikeRob

New Member

Excel Facts

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

MikeRob

New Member

sandy666

Banned - Rules violations

Power Query documentation - Power Query

Similar threads

Forum statistics

Share this page

Need Help with Match/IF/ISNUMBER problem.

MikeRob

New Member

Excel Facts

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

MikeRob

New Member

sandy666

Banned - Rules violations

Power Query documentation - Power Query

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock