IF,OR,Vlookup to pull multiple sheet data

[paradise2sr]

I have mentioned formula in K3:KO but I could not able to get the desired result as stated in B:G.

Pls help me what is wrong in the syntax.Also pulling list as unique from different sheet.

[Jacc]

The IFERROR logic you are trying to use will not work, it will not generate an error just because the result cell is empty. Also, when putting minus in front of VLOOKUP you have to wrap it with parenthesis, I got many crashes because of this.

This seems to work but I didn't implement the check for empty input cell. I did implement INDIRECT so you can just drag the formula to the right and it will work.

Please Login or Register  to view this content.

Not sure about all those zeroes in the Sheet3 results.

[paradise2sr]

Thanx a lot.

Lastly, how to combine list A column data of each worksheet by formula without any duplicates which manually I have done in J column.

[Glenn Kennedy]

This is easier on the eye!! But it's still Jacc's formula.

[David A Coop]

Hi paradise2sr,

I took a slightly different approach to Jacc because I assumed that you would not have Sheet1, Sheet2 as headings.

My formula was =IFERROR(VLOOKUP($B3,Sheet1!$A$2:$C$20,2,0)-VLOOKUP($B3,Sheet1!$A$2:$C$20,3,0),"") - and editing the Sheet1 component.

If you want to follow Jacc's suggestion, I recommend =IFERROR(VLOOKUP($J3,INDIRECT(K$2&"!$A$3:$C$8"),2,0)-VLOOKUP($J3,INDIRECT(K$2&"!$A$3:$C$8"),3,0),"") which is a little shorter, and gives blanks instead of zeros.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[paradise2sr]

Yeah I got but kindly refer to my post #3.

Here I am expecting to get formula for listing A column data without any duplicates into one sheet which I have mentioned in summary sheet in J column. Rest above formula are fine and thanx for all of u.

[David A Coop]

Didn't see your post #3 until now.

I think the answer has to be using VBA.

Here are a couple of macros which will loop through your worksheets, create a list from Column A on each sheet to Column A on the SUMMARY sheet.

The second macro then sorts the list in alpha order, and removes the duplicates.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Please Login or Register  to view this content.

[Jacc]

That solved the problem, well done!
Nevertheless, there are a few details in the code that triggered me to go all Yoda here.

Option Explicit is strongly recommended as it forces you to declare variables.
Makes troubleshooting soooooooo much easier. It also makes the code run faster but in this case it probably wouldn't be noticeable.

Another minor improvement is the addition of switching calculation on and off. I think this dataset is too small for anyone to notice the timedifference but on large data sets it makes a difference.

Copy paste is a heavy, slow process which is rarely needed. I'm doing the same thing here with just =. This will make a big difference on this workbook.
By removing the copy paste, it is also possible to remove the Activate command in most places. Activate and Select are also very slow commands, they also make a big difference even on a small workbook.
Finally I removed a lot of parameters for the sort method since those are correct by default. Looks neater, that's all.
In a half donkey attempt to be correct I passed the Summary worksheet as a parameter to the sorting Sub.

Faster the code now is.

Please Login or Register  to view this content.

[David A Coop]

That solved the problem, well done!
Nevertheless, there are a few details in the code that triggered me to go all Yoda here.

Thanks Jacc (or Yoda if you prefer). I am sort of self taught with VBA, and can see the wisdom of what you say. Some really good points I hope I can learn from and remember.

I think another improvement could be added. If we picked up each sheet name as we go through the ws loop, the column headings on the Summary sheet could be created, thus saving the user the need to do this. Obvious benefit of avoiding typos etc.

How would you code this?

DAC

[paradise2sr]

Great Thanx Jacc & David.

Infact in real life I am dealing with a large data set more than 8000 rows.Since here it is limit and due to privacy only a sample I am able to post.And Rest copy paste you have already explained.

And Thanx for your more improved faster code thinking for large data set without any helper column.


This solved my issue.

[David A Coop]

You're welcome paradise2sr.



DAC

[David A Coop]

Sorry dup post

[Jacc]

Good to hear my code is appreciated!

Picking up the sheets as you go along makes a lot of sense considering the large dataset. I'm guessing the sheet numbers is the way to go:

Please Login or Register  to view this content.

Also the formula should be added via VBA for the same reason.
Getting the headers in place is fairly straight forward. As for the formulas the macro recorder is a good help. Simply start recording, hit F2 in a formula cell, then enter, then stop recording. The formula is now ready to be copied from the latest recording in R1C1 format. The R1C1 format looks the same throughout the range so once the range to where the formula should be applied has been established you apply the formula to the whole range at once like this:

Please Login or Register  to view this content.

.

Another question to ask is whether the formula should be there at all? Afterall, the same calculations can be made in VBA. An 8000 line recalc every now and then may slow things down.
I would probably go for the formula solution anyway because it only takes one tiny line of code to convert from formula to static values should that be needed:

Please Login or Register  to view this content.

Using the ' in the code it is easy to switch this line on and off to find out what works best for you.