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?
Eitel DagninConnect With a Mentor IT 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.
NorieVBA ExpertCommented:

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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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. :)
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:
All details within link of the answer.
All Courses

From novice to tech pro — start learning today.