Combining multiple rows into single row

[neo2049]

I hope someone can help.

I have the following table in excel:

1	ID	CREATED DATE	FIELD	NEW VALUE	OLD VALUE
2	001	2020/01/15	Language	German	French
3	001	2020/01/15	Latest Outlet	a001	a002
4	001	2020/01/15	Latest Outlet	Parndorf	Provence
5	002	2020/01/16	Language	French	English
6	002	2020/01/16	Latest Outlet	a002	a003
7	002	2020/01/16	Latest Outlet	Provence	Ashford

I would like to extract the new data into a new table based on date and New Value:



ID Centre Language

001	Parndorf	German
002	Provence	French

I have no idea even where to begin.

Thanks in advance.

 

[PCL]

Have look on Pivot Table and on AutoFilter

 

[ajetrumpet]

well there's an obvious question to ask here, Neo. Your new name table example is confusing because you've basically transposed the data from the old into the new. so what you've essentially done is the same thing that a person in ms access would do if they want to show financial data, per period, using a cross tab query. is that what you want? furthermore, the question must be asked: what does "language" and "latest outlet" mean? because your new table example indicates that the "old value" comes from the original record entry and the "new value" is the latest record entry. per date. is that true?

 

[neo2049]

well there's an obvious question to ask here, Neo. Your new name table example is confusing because you've basically transposed the data from the old into the new. so what you've essentially done is the same thing that a person in ms access would do if they want to show financial data, per period, using a cross tab query. is that what you want? furthermore, the question must be asked: what does "language" and "latest outlet" mean? because your new table example indicates that the "old value" comes from the original record entry and the "new value" is the latest record entry. per date. is that true?

Sorry, I should've been clearer. I would like to take the values from New Value with Latest Outlet = Centre. The 2nd table didn't format the header row properly. Transposing won't work.

 

[ajetrumpet]

well I do virtually everything by writing code, neo. like I explained earlier. it's very possible that you can do it with pivot-related features like PCL said, or there might even be built-in functions that can do the job. but I posted a solution for you, it would be nothing but a single VBA script to capture the data and put it into a new table on a new sheet, reformatted. what method do you want to go with? I'm headed home now but have to stop quite a few places on the way. I won't be back online until later tonight.

 

[sandy666]

you can try Power Query aka Get&Transform (on Windows, Mac is not supported)

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"ID", type text}, {"CREATED DATE", type date}, {"FIELD", type text}, {"NEW VALUE", type text}, {"OLD VALUE", type text}}),
    Filter = Table.SelectRows(Table.RemoveColumns(Type,{"CREATED DATE", "OLD VALUE"}), each ([NEW VALUE] <> "a001" and [NEW VALUE] <> "a002")),
    Outlet = Table.FillUp(Table.AddColumn(Filter, "Latest Outlet", each if Text.Contains([FIELD], "Lat") then [NEW VALUE] else null),{"Latest Outlet"}),
    Language = Table.FillDown(Table.AddColumn(Outlet, "Language", each if Text.Contains([FIELD], "Lang") then [NEW VALUE] else null),{"Language"}),
    RD = Table.Distinct(Table.RemoveColumns(Language,{"FIELD", "NEW VALUE"}))
in
    RD