Combining multiple rows into single row

neo2049

New Member
Joined
Jan 19, 2020
Messages
2
Office Version
365
Platform
Windows, MacOS
I hope someone can help.

I have the following table in excel:


1IDCREATED DATEFIELDNEW VALUEOLD VALUE
20012020/01/15LanguageGermanFrench
30012020/01/15Latest Outleta001a002
40012020/01/15Latest OutletParndorfProvence
50022020/01/16LanguageFrenchEnglish
60022020/01/16Latest Outleta002a003
70022020/01/16Latest OutletProvenceAshford
I would like to extract the new data into a new table based on date and New Value:



ID Centre Language

001ParndorfGerman
002ProvenceFrench
I have no idea even where to begin.

Thanks in advance.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
Have look on Pivot Table and on AutoFilter
 

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
365, 2016, 2007
Platform
Windows
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

New Member
Joined
Jan 19, 2020
Messages
2
Office Version
365
Platform
Windows, MacOS
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

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
365, 2016, 2007
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,006
you can try Power Query aka Get&Transform (on Windows, Mac is not supported)
filters.png

Code:
// 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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,234
Messages
5,443,269
Members
405,222
Latest member
Summer01

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top