Extract Specific Data from one sheet to another

[simonmiller]

Hi All,

I have a price list from a supplier with 2000+ items. I only sell approx

I have the list of items and SKu that i have listed. I want a “easy” want to extract only the items from the supplier price list matching the items i have on my listed items i sell.

Does this make sence?

Supplier Price list:

Sku	Discription	Price
Sku1	Items 1	1.00
Sku2	Items 2	2.00
Sku3	Items 3	3.00

My List

Sku	Discription	Price
Sku1	Items 1	0.05
Sku3	Items 3	1.05

New extraction
I want my new list to take the data from My list look for the corosponding sku in suppliers price list and extract only those lines.

 

[JEC]

With power query;

Merge your list with the suppliers list with an inner join

 

[etaf]

what version of excel you have

you can do with a helper column using countif()

in a helper column - something like - i'm sure it can be done without the helper column , but really need to know your excel version

EDIT
see below - for one formula, no helper column needed
=FILTER(A2:C4,COUNTIF(H2:H5,A2:A4))


=COUNTIF($H$2:$H$3,A2) where H2:H3 is you list of wanted SKU

then use the FILTER function - if you have a later version of excel

all on 1 sheet - just to show easily with xl2bb - but can be on different sheets

Book3
	A	B	C	D	E	F	G	H
1	Sku	Discription	Price		helper			my sku
2	Sku1	Items 1	1		1			sku1
3	Sku2	Items 2	2		0			sku3
4	Sku3	Items 3	3		1
5
6
7	MY List
8	Sku1	Items 1	1
9	Sku3	Items 3	3
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=COUNTIF($H$2:$H$3,A2)
A8:C9	A8	=FILTER(A2:C4,E2:E4=1)
Dynamic array formulas.

no helper column needed, row 12,13,14 below

Book3
	A	B	C	D	E	F	G	H
1	Sku	Discription	Price		helper			my sku
2	Sku1	Items 1	1		1			sku1
3	Sku2	Items 2	2		0			sku3
4	Sku3	Items 3	3		1
5
6
7	MY List
8	Sku1	Items 1	1
9	Sku3	Items 3	3
10
11
12	one formula no helper
13	Sku1	Items 1	1
14	Sku3	Items 3	3
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=COUNTIF($H$2:$H$3,A2)
A8:C9	A8	=FILTER(A2:C4,E2:E4=1)
A13:C14	A13	=FILTER(A2:C4,COUNTIF(H2:H5,A2:A4))
Dynamic array formulas.

 

[alansidman]

Here is the Power Query way to do it

let
    T1 = Excel.CurrentWorkbook(){[Name="Supplier"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(T1, [PromoteAllScalars=true]),
    T2 = Excel.CurrentWorkbook(){[Name="MyList"]}[Content],
    #"Promoted Headers1" = Table.PromoteHeaders(T2, [PromoteAllScalars=true]),
    MQ = Table.NestedJoin(#"Promoted Headers", {"Sku"}, #"Promoted Headers1", {"Sku"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(MQ, "Table2", {"Price"}, {"Table2.Price"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Table2.Price] <> null))
in
    #"Filtered Rows"

Book1
	J	K	L	M	N	O	P
1	Sku	Discription	Price		Sku	Discription	Price
2	Sku1	Items 1	1		Sku1	Items 1	0.05
3	Sku2	Items 2	2		Sku3	Items 3	1.05
4	Sku3	Items 3	3
5
6	Sku	Discription	Price	Table2.Price
7	Sku1	Items 1	1	0.05
8	Sku3	Items 3	3	1.05
Sheet1

 

[JEC]

With an inner join you have the correct output directly