Member Avatar for dandixon

Have a table in column a i have a list of part numbers p101, p101, p102, p102, p102, p103, p104 etc

part no alternative
p101 a178
p101 a196
p101 a192
p102 a135
p103 a165
p103 a145
p104 a179

i need to have it like this but i cant work out the equation, anyone help

part no alternative
p101 a178 a196 a192
p102 a135
p103 a165 a145
p104 a179

  • 3 Contributors
  • 2 Replies
  • 91 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

This is a classic "group rows by key and concatenate text" situation. 's idea of exporting to a program and 's suggestion of SQL are both valid, but if you want to stay inside Excel there are three practical options depending on your Excel version: formulas (Excel 365/2021), Power Query (Get & Transform), or a small VBA routine for older builds.

Formula (Excel 365/2021 — fast and dynamic). Create a unique list of parts (for example with UNIQUE) and next to each unique part use TEXTJOIN+FILTER to collect alternatives. Example (parts in A2:A100, alternatives in B2:B100, unique part in D2):

=TEXTJOIN(" ", TRUE, FILTER($B$2:$B$100, $A$2:$A$100 = D2))

This produces a single cell with alternatives separated by spaces (use "," for commas). Wrap UNIQUE or SORT as needed.

Power Query (Excel 2016+). Load the table via Data > From Table/Range. In the Query Editor use Group By on the part column and combine the alternatives with Text.Combine (or group to All Rows then add a custom column like Text.Combine([AllRows][Alternative], " ")). Close & Load to get a refreshable consolidated table.

VBA (older Excel or batch runs). A short macro using a Dictionary will loop the rows, aggregate alternatives, and write a new sheet. Backup the workbook before running macros and trim/normalize values first to avoid accidental duplicates.

Recommended Answers

All 2 Replies

Member Avatar for Adak

I don't know if you can do this in Excel easily, but it's trivial for a program to take your data, make these changes, and give it back to you.

You know how to export a file from Excel, in text format?

Do you know how to import a file back into Excel?

Member Avatar for debasisdas

Do you know how to write SQL ?

That will be the easiest solution for your problem.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.