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,

you could do a double vlookup by using the ifna function. I do that all the time. — Ames, Dec 30 '17 at 04:03

score 0 · Answer 1 · answered Dec 30 '17 at 03:50

0

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.

answered Dec 30 '17 at 03:50

Ctznkane525

7,297
3
16
40

I did see the post after you mentioned it. However, I was thinking since my data is around 10k-15k, would that be applicable. Sorry for not mentioning about it earlier. – Elisha Robert Joseph Dec 30 '17 at 04:00
Size is almost always i**rrelevant** with Excel and Access, yet people always feel it necessary to share the quantity of data they have, as if that makes it harder to solve. :-) _"I need a haircut_ but I need a _really good barber_ since I have _100,000 hairs!_" (Sorry just teasing; it's not just you, I see that constantly.) :-) – ashleedawg Dec 30 '17 at 05:01

score 0 · Answer 2 · answered Dec 30 '17 at 04:28

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)))

How to automate excel task

2 Answers2