VBA VLookup - Copy Multiple Columns & Select Cells Where Data is to be Pasted


Question also originally posted here:

Link: https://www.tek-tips.com/viewthread.cfm?qid=1785624

I have 2 sheets in a workbook

1) Consolidated
2) 3rdPartySummary

The layout of the sheets are as follows:



From here:

The vlookup needs to match the EmpID from the Consolidated sheet with the EmpID on the 3rdPartySummary sheet.
Copy the AppName (Column A Row 1 on the 3rdPartySummary sheet) and the Status (Column C Row 1 on the 3rdPartySummary sheet), put the AppName into the next available column in Row 1 on the Consolidated sheet and the Status into the corresponding AppName in Row 2

Expected output on Consolidated Sheet:

I have now realized something though...

In the consolidated sheet where the vlookup will be performed, there will sometimes be multiple rows with different empID values..

This could be a possible situation:

In this case, I presume it would be best to do the vlookup for one empID and produce this result:

Then before the next one is done, a blank row is inserted and the next vlookup is done to produce this:


I highlighted the cells to show that it may be possible that not all empID's will match for each app.. So it might be better to have the AppName in each row for every empID there is??

Alternatively, I don't know how we could produce this output:

Hope this makes sense to you as it did to me..
Eitel DagninIT Security AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:

Can you attach a sample workbook?
Roy CoxGroup Finance ManagerCommented:
I would suggest using. Find or if the data is in a Tabular Format then use AutoFilter. As Norie says a sample workbook would help
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Norie and Roy,

Kindly see the updated question above.. I have significantly amended the question.. Hope it makes more sense now. :)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roy CoxGroup Finance ManagerCommented:
I still cannot see an example workbook
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Roy,

Apologies, I have attached the file now.
Eitel DagninIT Security AdministratorAuthor Commented:
Hi All,

Just an update to the question..

Got this answer from the other thread: https://www.tek-tips.com/viewthread.cfm?qid=1785624

I converted the data on the 3rdPartySummary sheet into a structured table and left it named as Table1 for the sake of testing.

I then went to the Consolidated sheet and put the app names in the first row and for each empID (Employee Number) I put this formula in each cell up to the last app name.

This is what the formula looks like:

=IFERROR(INDEX(Table1[Status],SUMPRODUCT((Table1[App Name]=B$1)*(Table1[Employee Number]=$A2)*(ROW(Table1[Status])))-1,1),"")

What I am trying to do now is put the formula into a macro and apply the formula across the cells.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eitel DagninIT Security AdministratorAuthor Commented:
All details within link of the answer.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.