Match Type and Count with IDs and organize my Type and Count

Column ID will vary as far as the number of IDs it will have. The color red helps match the ID with the Type & Count in column C.

The Data Source has the ID column and the Type the ID will have and a count

Need a macro that will match the Type & Count by ID.

The Data Source has more than 15k rows with the ID (range:1 - 60000) rows and Type & Count columns; matching this manually takes days as the counts change on bi-weekly basis.

Please refer to sample file...

Thanks for the reply on this request.

So now that the Power Query has separated the ID, Attribute (Type) and Value (Count) into 3 columns, how do I accomplish the grouping of applicable Type/Count based on the IDs in the ID column as per the Data tab sample?

The ID column will be a manual input by me. It could be 1 ID or 5 IDs or more. What is very time consuming and labor intensive is the grouping in the Type & Count column. Based on the IDs in the Data tab, I need for the VBA to go to the Data Source tab and identify the type/count per each ID in the Data tab ID column and group them the Type & Count column. It's color coded (black, red, black, red...) so that we can match tge groupings with the IDs.

Here is a VBA solution since the thread has not been designated as solved. Let us know if the groupings align to your expectations.

Please Login or Register  to view this content.

This is a great start (the alternate color). This will reduce my workload by 50%. What would reduce it by 90% is where the ID row that has more than 1 ID, the grouping would be in the same row, in the Type & Count column as per the sample file. If that's not possible, I'll just filter, copy and paste the groupings into that cell... thx so much ...

How do we know which ID’s to combine?

Hi maniacb...

If you notice in my attached file column B (ID), those IDs I have already manually input the ID or IDs (in most cases it may only have 1 ID, in some cases it may have 10). However, once I've manually input the ID(s) into that column, it mostly remains static.

My challenge is to get the Type & Count per ID in the ID column and group them in the Type & Count column in the corresponding row.

Take for example B4 - that only had 1 ID so the results in the Type & Count is 2 different type and their count.

However, B5 has 2 IDs. Now I need to get the Type & Count per ID and paste it in C5, in the top to down order. So you can see in C5 that the black font corresponds to the 1st ID and the red font corresponds to the 2nd ID. Since the Type & Count is very dynamic (changes weekly) that's where I need the help on.

If you share your static ID column, we may be able to use it as a reference to create your desired result. Please share that column of ID’s.

In the attached file (02-25-2021, 05:20 PM), the Data tab, column B (ID), that's the column of static IDs. My master currently has 350 rows, each with a static ID or IDs per cell. That's the column the macro will reference to create my desired result in column C (Type & Count) from the Data Source tab... I only provided a sample of the static IDs in the attached file...

Thanks, this works perfectly. It's amazing how my workload has gone down to 3 mins max from almost a day... thanks to everyone who pitched in to resolve this for me. Excel Forums is amazing...