Combine matching data in new column

[jamobe]

Hi

I'm hoping someone can help me find a solution. I am trying to figure out how I can achieve the results below. Where there is a matching ASIN in column B and has the fulfillment of FBA, I would like this SKU in column A to then be entered into a new column 'LINKED SKU' so it is matched with the FBM SKU. The desired result is below. I'm racking my brains trying to figure this out and hope someone can help me please. I hope this makes sense. Many Thanks!!

SKU	ASIN	Fulfillment
SKU1	1234	FBM
SKU1A	1234	FBA
SKU2	5678	FBM
SKU2A	5678	FBA
Desired Result
SKU	LINKED SKU	ASIN
SKU1	SKU1A	1234
SKU2	SKU2A	5678

 

[RoryA]

I think you would just merge two queries using the ASIN number. The first query only includes FBA items, and the second excludes them.

 

[ExceLoki]

from the small sample data, this should work for what you're trying to do.
---------------

Book1
	A	B	C	D	E	F	G
1	SKU	ASIN	Fulfillment		SKU	LINKED SKU	ASIN
2	SKU1	1234	FBM		SKU1	SKU1A	1234
3	SKU1A	1234	FBA		SKU2	SKU2A	5678
4	SKU2	5678	FBM
5	SKU2A	5678	FBA
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=IF(MATCH(G2,B$1:B$5,0)>1,XLOOKUP(G2,B$1:B$5,A$1:A$5,"na",0,-1),"")

 

[JGordon11]

This will work if the names of the linked SKUs are always longer than the primary SKU name.

let
    Source = Table.AddColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Sort Col", each Text.Length(_[SKU])),
    Group = Table.Group(Source, {"ASIN"}, {{"Linked SKU", each 
        let t = Table.Sort(_,{{"Sort Col", Order.Ascending}}) in {t[SKU]{0}} & {Table.SelectRows(Table.Skip(t,1),(x)=> x[Fulfillment]="FBA")[SKU]}}}),
    tbl = Table.AddColumn(Group, "SKU", each _[Linked SKU]{0}),
    tbl1 = Table.TransformColumns(tbl, {"Linked SKU", each Text.Combine(List.Transform(_{1}, Text.From), ", "), type text}),
    Result = Table.ReorderColumns(tbl1, List.Reverse(Table.ColumnNames(tbl1)))
in
    Result

PQ linked SKU.xlsx
	A	B	C	D	E	F	G	H
1	SKU	ASIN	Fulfillment		SKU	Linked SKU	ASIN
2	SKU1	1234	FBM		SKU1	SKU1B, SKU1A	1234
3	SKU1A	1234	FBA		SKU2	SKU2A	5678
4	SKU1B	1234	FBA		SKU3		2468
5	SKU2	5678	FBM		SKU4	SKU4A	1357
6	SKU2A	5678	FBA
7	SKU3	2468	FBM
8	SKU3A	2468
9	SKU4A	1357	FBA
10	SKU4	1357	FBM
11
Sheet1