[VBA] Pulling data from all sheets in a workbook arranged in different columns

[eddyble]

Hi Guys - pretty stuck and not even sure what to google, so hoping some experts could help me out.

I've created a macro that pulls all Worksheet names and notes them down in a newly created 'Master_Sheet' in Row 1 (Sheet 1 -> Sheet x) starting from B1.
It then pulls a column from every Worksheet and puts that data into Column A putting each Sheet's data underneath the previous Sheet's data (apologies if this is unclear, output below).

Name	Sheet 1	Sheet 2	Sheet 3
A (Sheet 1)
B (Sheet 1)
C (Sheet 1)
A (Sheet 2)
D (Sheet 2)
B (Sheet 3)
F (Sheet 3)

The script then deletes duplicates in Column A (i.e D is now A5 and F is now A6).

Here's where I'm stuck. Each 'Name' on each Sheet corresponds with some number a few Columns over (Example below). I would like some way to get that number and 'match' it to the 'Name' on the Master_Sheet in the corresponding Sheet Column.

Sheet 2

Name
A		3
D		4

Essentially, the output line C2 on the Master_Sheet would be = 3 and C5 = 4

Sheet 3

Name
B		5
F		6

Master_Sheet would show D3 = 5 and D6 = 6

This would loop for all Sheets in the workbook.

Apologies for the lengthy explanation, hopefully it's clear enough that someone can lend me a hand as i'm properly stuck.

Thank you in advance -

 

[eddyble]

I solved it - it's not an eloquent solution... but it involved a lot of vlookups. Either way, the dataset will never be too large for this to be cumbersome.