Consolidate Data

[zabuzaa]

HI All,

I've read through a number of posts and saw that the folks here do an awesome job helping people like me with excel questions. I'm hoping someone here can help me with an issue im facing.

I'm trying to consolidate data. I will try to explain my situation below

Example
A1=Blue B1=5
A2=Blue B2=10
A3=Blue B3=5
A4=Green B4=2
A5=Green B5=4
A6=Green B6=2
A7=Green B7=2
A8=Yellow B8=3
A9=Yellow B9=6

What kind of formula could I use that will allow me to consolidate all the values for Blue into one cell ( C1= 5,10), all the values for Green into one cell (C2=2,4) and so on...

Is that possible?

[tigeravatar]

zabuzaa,

Welcome to the forum!
It looks like you want to concatenate just the unique values from column B that have the same color in column A. Unfortunately, there is no native function in Excel that can do this because CONCATENATE doesn't work with arrays.

That being said, you can use VBA to accomplish what you're looking for. Attached is an example file based on the criteria you described. In cell E2 and copied down is this formula:

Please Login or Register  to view this content.

ConcatUniqueIf is a UDF (User Defined Function) that has the following code:

Please Login or Register  to view this content.

To view the code, just press Alt+F11 to bring up the VBE (Visual Basic Editor) and double-click on Module1.

[zabuzaa]

@ tigeravatar

Thanks, I'll give it a shot.

[zabuzaa]

I didn't get to try it today because I was swamped with other matters but tomorrow i'll give it a shot for sure. One thing i forgot to mention is the size of the data (not sure if it matters or not). The amount of rows in column A is 136273 and the same for column B. Would that make a difference in the forumula used?

[tigeravatar]

You would just adjust the ranges to suit, UDF wouldn't change

[zabuzaa]

Thanks for all your help so far. I've modified the ranges for column A and B to meet the amount of rows im working with. Any reason why an #N/A! would appear in column E. Everything starts to work find until I get to a certain row (E395) and then it starts producing #N/A!. All the formatting is the same for all the data in columns A and B.

[tigeravatar]

I'd have to see a sample file that is experiencing the issue

[zabuzaa]

Here is a sample file

[tigeravatar]

It's because in your workbook, you have values going down to row 5000, but you only feed the formula values from row 1 to 1001. So when it needed to find values that were beyond row 1001 (like in A1300), it wasn't fed that range and resulted in the error.

I did make some changes to the UDF to make it more efficient (calculate faster), and I am attaching the modified version of the workbook.

[zabuzaa]

Thanks so much for your help. You truly saved me a ton of time.

[tigeravatar]

You're very welcome