Excel - Consolidate IDs and display data horizontally

Hi @ Joe Daniel,

See if the formula below helps you achieve the result you want.

Hope this helps.

IlirU

=LET(
  gb, HSTACK(BYROW(A2:B5, ARRAYTOTEXT), BYROW(C2:E5, ARRAYTOTEXT)),
   d, IFNA(TEXTSPLIT(TEXTJOIN(";",, BYROW(GROUPBY(TAKE(gb,, 1), DROP(gb,, 1),
      ARRAYTOTEXT,, 0), ARRAYTOTEXT)), ", ", ";"), ""),
      IF(ISNUMBER(--d), --d, d)
)

Hi,

This M code in PQ works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Source", "#", "MD", "Index"}, {"Source", "#", "MD", "Index"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Count", {"ID", "Index"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}, "en-IN"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Hi @Joe Daniel,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, you can try using Power Query to achieve your expected results since Power Query can help you update automatically if new rows are added into the data table:

• First, I imported your data like this. Then select any cell in the range > press Ctrl + T to convert to a table and also check the box "My table has headers" > OK

• Then click anywhere inside the table > go to Data tab > Get Data > From other sources > From Table Range > you will see Power Query opening
• In the Power Query editor, click the ID column > Transform > Data Type > Text (since your ID data is more than 15 digits, so you need to keep it as Text so Excel doesn't round it)

• Next step, go to Home > Group By > set Group by: ID > New columns name: Allrows > Operation: All Rows > OK > you will have two columns left: ID and Allrows

• Now go to Add Column tab > Custom Column > name the column Groupedwithindex > paste this formula Table.AddIndexColumn([Allrows], "Entry", 1, 1, Int64.Type) > OK

• Now you will have three columns ID, Allrows and Groupwithindex. Right click the Allrows column and remove it
• Click the expand icon on Groupedwithindex and select these columns to expand and also uncheck "use orginal column.." > OK

• Hold Shift and select three columns SOURCE, # and MD then go to Transform > Unpivot Columns > it will become two columns like below (Attribute and Value)

• Then go back to Add Column > Custom Column > name the column PivotKey > paste this formula [Attribute] & "_" & Text.From([Entry]) > OK

• Now you will see five columns ID, Entry, Attribute, Value and PivotKey. Right click and remove two columns Entry and Attribute
• Last step, select the PivotKey column > Transform tab > Pivot Column > Set the dialog as same as below and then OK

• Now go to Home tab > Close and Load > you will have the result as your ideal state.

Whenever any new data added, you just need to click inside the range > Query tab > Refresh and new data will be updated into your table.

Hope this information will help. Please follow these steps and let me know if it works for you. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you.

Thank you for your patience and understanding. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Use a formula-based “unpivot → re‑pivot” approach so each ID appears once and its related rows are spread horizontally.

Assume the current data is in Sheet1 with headers in row 1:

• A: ID
• B: SOURCE
• C: #
• D: MD

1. Create a helper column to count entries per ID

In E2 enter and copy down:

=COUNTIF($A$2:A2,A2)

This gives 1 for the first row of an ID, 2 for the second row of the same ID, etc.

2. Build the horizontal layout

On a new sheet (or to the right of the data), create the output table.

In G1 put ID. In H1, I1, J1, K1, L1, M1, etc., put the headers you want, for example:

SOURCE_1 #_1 MD_1 SOURCE_2 #_2 MD_2 SOURCE_3 #_3 MD_3 ...

Unique IDs

In G2 enter:

=IFERROR(INDEX($A$2:$A$1000, MATCH(0, COUNTIF($G$1:G1, $A$2:$A$1000), 0)), "")

Confirm as an array formula if required by the Excel version (Ctrl+Shift+Enter), then copy down until blanks appear. This lists each ID once.

3. Pull each nth record for each ID into columns

For SOURCE_1 (H2):

=IFERROR(INDEX($B$2:$B$1000, MATCH(1, ($A$2:$A$1000=$G2)*($E$2:$E$1000=1), 0)), "")

For #_1 (I2):

=IFERROR(INDEX($C$2:$C$1000, MATCH(1, ($A$2:$A$1000=$G2)*($E$2:$E$1000=1), 0)), "")

For MD_1 (J2):

=IFERROR(INDEX($D$2:$D$1000, MATCH(1, ($A$2:$A$1000=$G2)*($E$2:$E$1000=1), 0)), "")

Adjust the =1 to =2 for SOURCE_2/#_2/MD_2, to =3 for SOURCE_3/#_3/MD_3, and so on. Copy these formulas down for all IDs.

Each ID now occupies a single row, and its 1st, 2nd, 3rd, … related records are laid out horizontally. IDs with fewer entries simply leave later groups of columns blank.

If the maximum number of entries per ID is large, the same pattern can be extended as far as needed.

References:

• How to convert multiple rows and columns to columns and rows in Excel
• Top ten ways to clean your data