VBA Extract Unique trade id with date wise

[Malleshg24]

Hi Team,

Columns A Contains Trade ID and Columns E Contains Date,

My Task is to take only Unique Trade ID in each row from Range(I3) to downward. and update its file name in date wise columns.
if repetation of trade ID in any column, dont add extra row in output,But their file name should be taken in respective Date Column.

Date column may increase as per unique dates from M1 Onward.
attached is the spreadsheet with input and expected output from I Column onward.



Regards,
mg

 

[AlanSidman]

What is the source for the Trade ID? How does it relate to the data in the first table?

 

[rahulshewale1]

hi, @Malleshg24 ,


See if is ok ?
Solution Using Helper Column


Regard
Rahul Shewale

 

[AlanSidman]

I achieved the following layout using Power Query.
Data Range

	A​	B​	C​	D​	E​
1​	Entity Name​	XYZ File Name​	2/20/2020​	2/22/2020​	2/24/2020​
2​	Capegemini​	xyx​	​	pqr​	​
3​	Capegemini​	xyx​	​	pqr​	​
4​	Capegemini​	xyx​	​	pqr​	​
5​	Capegemini​	xyx​	​	pqr​	​
6​	Capegemini​	xyx​	​	pqr​	​
7​	Capegemini​	xyx​	​	pqr​	​
8​	Capegemini​	xyx​	​	pqr​	​
9​	Capegemini​	xyx​	​	pqr​	​
10​	Capegemini​	xyx​	​	pqr​	​
11​	HCL​	rst​	​	​	rst​
12​	HCL​	rst​	​	​	rst​
13​	HCL​	rst​	​	​	rst​
14​	HCL​	rst​	​	​	rst​
15​	HCL​	rst​	​	​	rst​
16​	HCL​	rst​	​	​	rst​
17​	HCL​	rst​	​	​	rst​
18​	HCL​	xyx​	​	pqr​	​
19​	Infosys​	xyz​	abc​	​	​
20​	Infosys​	xyz​	abc​	​	​
21​	Infosys​	xyz​	abc​	​	​
22​	Infosys​	xyz​	abc​	​	​
23​	Infosys​	xyz​	abc​	​	​
24​	Wipro​	xyz​	abc​	​	​
25​	Wipro​	xyz​	abc​	​	​
26​	Wipro​	xyz​	abc​	​	​
27​	Wipro​	xyz​	abc​	​	​
28​	Wipro​	xyz​	abc​	​	​

Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity Name", type text}, {"ABC File Name", type text}, {"XYZ File Name", type text}, {"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Date", type text}}, "en-US")[Date]), "Date", "ABC File Name"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

 

[Malleshg24]

Hi RahulShewale and AlanSideMan,

Thanks a lot for your help on this,
I am getting data through server, expected output not possible through pivot hence doing it manually.
I will restructure my question , and resend it Thanks

Regards,
Mallesh