-1

enter image description here

Hey All on my excel sheet there is a column that i need to concatenate. I need the combine all the rows in the same colors into one row, I need to do this for a list of 10000+ rows. Any ideas?

ALSO THE COLORS I added in to explain what I need to combine, there are no colors in the spreadsheet.

Origin Sux
  • 11
  • 2
  • 1
    Two comments: First, your screenshot only shows a few rows each of which has a unique color. Second, what do you plan to do for the other columns in the rows you want to concatenate? – Tim Biegeleisen Jan 14 '16 at 14:06
  • Honestly not even sure where to start, I was goign to try an iF statement where if the next column is blank then concatenate. But if I can get a Result of the concatenated rows, I will go and delete all Blank A Rows, then replace the non concatenated row with the concatenated one. – Origin Sux Jan 14 '16 at 14:15
  • Also the color i added myself just to show what I need to combine, the real document has no color. – Origin Sux Jan 14 '16 at 14:42

2 Answers2

0

If you are willing to use VBA it could be as simple as inserting this into a module and then using it with other regular functions to build your string:

Function WhatColor(r As Range) As Long
    WhatColor = r.Cells.Interior.Color
End Function

If it has to be a "somewhat" native function, check out the GET.CELL answer here.

Community
  • 1
  • 1
rohrl77
  • 3,277
  • 11
  • 47
  • 73
  • Wait the color I added in just to show you want I need to combine, there are no colors in the main sheet, i just colored it in to show an example of what I need to combine. And I'm willing to use any code to get this done, though not that experienced in coding. – Origin Sux Jan 14 '16 at 14:26
0

In a helper column (just any empty column, I Assume AA here, change that to your needs) starting with row 1 (I assume the colored column is F, change that to your needs):

=IF(LEN(A2),F1,F1&", "&AA2)
  • copy down till the end
  • copy whole column
  • paste as values only (to column F)
  • Set filter to show only rows which are empty in column A
  • select all
  • delete all
  • turn off the filter
  • finished :)
Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
  • The only thing is that sometimes i needs to concatenate 4 rows sometimes just 2, this formula seems to work if only 2 rows needs to be concatenated. Is there anyway to check if theres a blank row before it to continue concatenating? – Origin Sux Jan 14 '16 at 14:58
  • oh sorry... my fault :P `F2` needs to be the cell below in the helper column. – Dirk Reichel Jan 14 '16 at 15:01
  • Sorry i'm not sure what you mean by this. can you please explain a bit more? – Origin Sux Jan 14 '16 at 15:39
  • having the formula like this: `=IF(LEN(A2),F1,F1&", "&AA2)` works (if the column you are using is AA)... i just had `&F2)` instead of `&AA2)` at the end – Dirk Reichel Jan 14 '16 at 15:43
  • THANK YOU!!! this is GENIOUS!! Major thumbs up, even though i dont understand how this function works. Thanks alot – Origin Sux Jan 14 '16 at 15:45
  • It is like a simple `=A1&A2` in B1 and copy down (it will line up everything)... but the `IF` stops it every time there is text in column A in the row below itself... (also i switched it to work from the bottom to the top)... if you use "evaluate formula", it is pretty obvious (and easy to understand) :) – Dirk Reichel Jan 14 '16 at 15:59