Comparing Data from multiple spread-sheets and fish out wanted data into a separate spread-sheet organized per tab

[Harry_1234]

I have multiple tabs of data (named AB, BC, CD, EF, FG, GH, HI, IJ, JK, KL, LM, MN, NO, OP, PQ, QR, RS, ST, TU, UV, VW, WX, XY, YZ, Z00, Z01, Z02, Z03) in my "critical numbers spread-sheet" with wanted numbers per application identified in each tab but columns and rows are inconsistent. I have an other spread-sheet ("total inventory spreadsheet") with total inventory of all the numbers spread across two tabs along with the location name. I would like to compare my total inventory spreadsheet against wanted numbers spread-sheet and fish out all wanted numbers identified per location into it's own tab i.e. see if number from total inventory exists in "critical numbers spread-sheet", if so it goes into a separate tab (and the sheet name should be the location name from "total inventory spreadsheet" i.e. AB, BC etccc). Attached is what i am trying to accomplish? Also to note, critical numbers spread-sheet will have an extra two characters ("\+") in front of every number. Once I have the spread-sheet with all the wanted numbers identified with proper location code, I would like to specify a range, that gets assigned as forwarded value in sequential order for all the numbers across all the tabs.

 

[Marc L]

​

Well according to your post #5 'attachment' if I try anything the code will work only for this unique tab​

instead of a multiple tabs as described in the initial post​

so are you enough confident with your Excel / VBA skills to amend any VBA procedure in order to well fit it to your real workbooks ?​

​

Another point : with the bad idea to use Excel as a database software - as any is 50 times faster than Excel - the execution may last a while …​

 

[Harry_1234]

Sure I can try. I can make the source where the VbA code will be located to only have 1 tab but the second workbook where it needs to compare against will have multiple sheets and the results can be stored in the same source workbook but different sheet and I can take it from there. Long story short the column3 values in source workbook will need to be checked if it exists in second workbook. If it does then I want to arrange those wanted column3 values by location name.

​

Well according to your post #5 'attachment' if I try anything the code will work only for this unique tab​

instead of a multiple tabs as described in the initial post​

so are you enough confident with your Excel / VBA skills to amend any VBA procedure in order to well fit it to your real workbooks ?​

​

Another point : with the bad idea to use Excel as a database software - as any is 50 times faster than Excel - the execution may last a while​

 

[Marc L]

​

Or just link the 3 workbooks with a files host website …​

 

[Harry_1234]

​

Or just link the 3 workbooks with a files host website …​

Sure..Here is the link where the files are hosted. The summary workbook I uploaded is an example of what I anticipate the output to look like. Thanks a lot for your help with this.

MediaFire

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[Marc L]

​

As your profile is not complete so a very important point : on which platform(s) do you use Excel ?​

 

[Harry_1234]

​

As your profile is not complete so a very important point : on which platform(s) do you use Excel ?​

Win 7

 

[Marc L]

​

Good news : according to your attachment the execution should be pretty fast …​

​

The sheets in your 'Summary' workbook attachment are exactly the expected result as it seems to differ from your initial post ?​

 

[Marc L]

​

Another point : if one - or both - of the other workbooks are closed they can be easily opened​

if they are located in the same folder than the workbook containing the VBA procedure ?​

​

Or the VBA procedure must check first if both other workbooks are yet opened in order to avoid to raise any error …​

 

[Harry_1234]

​

Another point : if one - or both - of the other workbooks are closed they can be easily opened​

if they are located in the same folder than the workbook containing the VBA procedure ?​

​

Or the VBA procedure must check first if both other workbooks are yet opened in order to avoid to raise any error …​

All three workbooks will remain open but if VBA procedure performs a check first to see if both other workbooks are open to avoid any issues, that will be great.

 

[Marc L]

​

Ok, and about my post #17 according to 'Summary' workbook ?​