How to automate excel task

Question

This is an example excel data I am using. I have to lookup address age and preferred location with 7-8 different file every morning.

This is an example of the date which I receive to fill up.

| Unique No.| Name   | Address | Age | Preferred location | Time SLot |
|:----------|-------:|:-------:|:---:|:------------------:|:---------:|
| 1         | Amit   |        :|:   :|:                  :|:         :|  
| 2         | Ranjan |        :|:   :|:                  :|:         :|
| 3         | Rohit  |        :|:   :|:                  :|:         :|
| 4         | Rahul  |        :|:   :|:                  :|:         :|
| 5         | Neeraj |        :|:   :|:                  :|:         :|
| 6         | Sampan |        :|:   :|:                  :|:         :|

For eg:- Data of unique no 1 (name AMIT) is in file Mumbai and Data of unique no 2 (name Ranjan) is in file Delhi. Because of this, I do am not able to lookup all the data together.

| Unique No.| Name   | Address | Age | Preferred location | Time SLot |
|:----------|-------:|:-------:|:---:|:------------------:|:---------:|
| 1         | Amit   | Mumbai :|: 26:|:   Delhi          :|:  7      :|  
| 2         | Ranjan |   NA   :|: NA:|:    NA            :|:   NA    :|
| 3         | Rohit  |   NA   :|: NA:|:    NA            :|:   NA    :|
| 4         | Rahul  |   NA   :|: NA:|:    NA            :|:   NA    :|
| 5         | Neeraj |   NA   :|: NA:|:    NA            :|:   NA    :|
| 6         | Sampan |   NA   :|: NA:|:    NA            :|:   NA    :|

If I use lookup, and if the information is not available from 1 file, I then have to filter it and then apply lookup on NA columns to search from other files.

This all wastes around 30-45. This is all regular process for me and so is there any way to automate it. Also, after the data is automated, is there any way to auto pivot the data.

I did try looking but I did not get any revelent results. Appreciate your help.

This is an example table and my table is around 10K-15K. Sorry for not mentioning it,

Answer 1

For the one you want to get the IDs, parse through the first sheet, and get the location of each ID and it's row number into a dictionary: Does VBA have Dictionary Structure?

In your case, the ID is the key and the row number is the value.

When you go through the second sheet, use the dictionary to find the position of the ID in the second sheet, rather than doing a search.

Note that selecting from a dictionary is not like a traditional search, dictionaries usually generally O(1) as they are based on a hash of the data. That's why you'll see a performance boost.

Answer 2

Assuming Amit and Sasi's information are in different files, file1 and file2 respectively, by using the ifNA function, you can reuse the vlookup (or index match - which I use extensively) when the first lookup returns NA)

=IFNA(INDEX(File1[lookup value],MATCH(A2,File1[name],0)),INDEX(File2[lookup value],MATCH(A2,File2[name],0)))