Excel macro to deal with multiple entries of data for a single ID

Question

Howdy -

I have some experience with Excel. I haven’t used VBA from MS, and I haven’t written macros in any spreadsheet. I was given a new task with 4 years of data, and I need to find a way to sort and compile the data - in a manner I haven’t done before. Some of the files have over 30,000 rows of data.

The basics are:
Sheet 1 - raw data
Column B = member ID (with each member ID most likely appearing on multiple rows)
Column G = member’s evaluation for each appearance of member ID

Sheet 2 - compiled
Column A = member ID (only one occurrence to reference the raw data)
Columns B - Z (or more) = each evaluation of a the member (so a member could have 1 evaluation, or 20+) transferred from sheet 1

Image attached of the Raw Data sheet.

What I need to do is be able to take the 30,000+ entries in sheet 1 column A and remove the duplicates for sheet 2 column A - that is easy enough. But the tough part is to then find each evaluation for a member on sheet 1, and transfer that evaluation to sheet 2 in columns B - Z.

I’ve tried to work with INDEX and VLOOKUP, and have had a bit of success with them. But, those commands require the ID look-up to be fixed to a specific cell (i.e., $A$2). And I don’t have the time to change the ID look-up for upwards of 7,000 unique members in 20 files. I’m hoping a macro would be able to execute the search and transfer of all evaluations for member #1 and transfer them to sheet 2 row 2, then automatically move to the next member and transfer the evaluations for member #2 to sheet 2 row 3 (row 1 is the header row for both sheets).

Thanks for any help or advice you can offer!

Paul

Answer 1

Hi Paul

As per your description, it seems your desirable output will look like the one in the picture below

If so, we might have 4 ways to achieve your goals

Pivot Tables, Power Query, VBA macro, and Array Formulas.

From the picture you posted, it seems the Excel version you are using is from 2010 or 2013

If so.

1. Pivot Table solution

It **** will be a quick and dynamic solution.

a) Add a helper column to your original table to calculate the running count per ID with the formula =COUNTIF($B$2:B2,B2). and copy it down

Adapt ranges in the formula to your real scenario

b) Convert your table into an Excel Table and create a Pivot Table

Select any cell within the table >> go to Insert ribbon Tab >>Insert Pivot Table >> From Table/Range

c) Make sure the entire table is selected/highlighted. Set the Pivot table to another sheet.

d) To build the Pivot Table select and drag the highlighted fields to the areas as indicated in the picture below

and VOILA!!!

2) Power Query solution

As the Pivot table is a very dynamic way to manipulate your data.

In your case Power Query might be an Add-In you have to enable.

Please refer to this video for details on how to do it. https://www.youtube.com/watch?v=gwW2CDdvUUs

Do let me know if you are interested in a Power Query solution

3) Formula Solution

Using the same helper column (Count_ID) with a little twist in the formula

=B2&"Result-"&COUNTIF($B$2:B2,B2)

For the Output Table

Considering you have already the list of Unique ID

=IFERROR(INDEX(Sheet1!$G:$G,MATCH($A2&B$1,Sheet1!$I:$I,0)),"")

4) VBA macro solution

I haven't worked on that yet, but surely other colleagues in the forum might help you if you specifically want a VBA solution t your problem.

I hope this helps you and gives a solution to your problem,

Do let me know if you need more help.

Regards Jeovany

Answer 2

@Paul

I'm glad you found solution to your problem with my reply.

Regards

Jeovany