0

I am trying to search an ID in a Column (which will have this ID possibly multiple times) and then take the next Column of the found ID and put the text in a field comma separated. I think this Image is showing it better than I can with my broken english.

All this should happen automatically. With VBA or a Formula if possible.enter image description here

The left list should be translated to the right. Any clues on how to achieve something like this?

Community
  • 1
  • 1
Jay Claiton
  • 437
  • 5
  • 17
  • Actually I don't even have an Idea how to start. I could easily do this via MySQL but with Excel it is too much for me – Jay Claiton Feb 03 '16 at 14:09
  • 2
    See this question here: http://stackoverflow.com/q/33112330/5090027 What you are asking for is not possible in a single formula; it will require either VBA or a helper column. – Grade 'Eh' Bacon Feb 03 '16 at 14:13

2 Answers2

2

To do this without VBA, you can use a helper column which will pick up each additional letter to add to the list for a particular number, and then search for the full concanated cell associated with each number, for your result.

Note that I suggest you do your helper column from the bottom and copied up - this will make it easier for you to pull the concanated values at the end, without using an Array Formula.

Put the formula in C21 [assuming 21 is your last data row], and copy it up to the top of your list:

=IFERROR(INDEX($C$22:C22,MATCH(A21,$A$22:A22,0)),"")&","&B21

What this does is search column C for all rows beneath the current row, to see what the most recent concanated result for that number is. It then pulls that text value ["" if there is no prior match], and adds on the value in column B for the current row. As you copy it up, each individual number's 'concanenated text' will continue to grow, adding letters where the number values match.

Then to pull those results into your table on the right, simply do the following in E1 and copy down:

=INDEX(C:C,MATCH(D1,A:A,0))

This pulls in the first matching row which has the number value you're searching for, and returns the value on that row from column C. Since the first matching value would be the one which includes all concanenated values before it, this will be a complete listing of matches to that number.

Note also that this method puts the bottom rows' letter values at the begining of the concatenation string.

Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46
0

A pivot table can be usefull to you if order of your the data is not important.

To create a pivot table label your two columns in row 1 and follow below instructions i got from google.

On the Insert tab, in the Tables group, click PivotTable. ... In the Create PivotTable dialog box, make sure that Select a table or range is selected, and then in the Table/Range box, verify the range of cells. ... Do one of the following: ... Click OK.

Choose the ID column for your ROWS(drag drop in the pivottable fiedls) and the data column (column2) for your COLUMNS.

Be refreshing the pivot table (by right click) whenever you modify your data

steve biko
  • 171
  • 5