Greetings,
I am trying to match the data in multiple files using arrays (because vlookup takes too long).
For example: I have 100 source files (workbooks) each having the information for 1 day. Each file has a number of items and each item have multiple information columns, as of a normal database.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Workbook 1 - 20 of January
Item (identifier) Price Price2 Price 3
A 100 5 10
B 80 6 15
C 110 3 15
Workbook 2 - 21 of January
Item (identifier) Price Price2 Price 3
A 95 3 5
B 90 6 10
C 120 4 20
D 130 7 2
Workbook 3 - 22 of January
Item (identifier) Price Price2 Price 3
A 70 11 35
B 100 1 15
D 45 17 25</code>There are around 20 price columns, and thousands of items in each day (workbook). New identifiers might be added or removed in new days. My code gets this data and uses vlookup to match, as it adds new source files (if a file does not have an item that was there in the past, the information simply counts as zero).
My output in excel is:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Tab (Worksheet) 1 (price)
All the item Identifiers 20/01 21/01 22/01
A 100 95 70
B 80 90 100
C 3 4 -
D - 130 45
Tab (Worksheet) 2 (price2)
All the item Identifiers 20/01 21/01 22/01
A 5 3 11
B 6 6 1
C 110 120 -
D - 2 25</code>And so on.
First problem is how to compare the identifiers in each file, and add the new ones to the end of the output list (each time a new day workbook is included).
Second is that, just doing the current procedure, the code takes too long (more than 20 hours). If I transform this information (each file separately) to an array, how can I search the information inside the array, match it and past it to the output file? Is there a faster option than vlookup?
If not, is it possible to use it to get multiple results are once? (instead of running 1 lookup for each column of each identifier, run i lookup for each identifier only and return all the columns at once, since they are always the same)
Any help will be deeply appreciated.
I am trying to match the data in multiple files using arrays (because vlookup takes too long).
For example: I have 100 source files (workbooks) each having the information for 1 day. Each file has a number of items and each item have multiple information columns, as of a normal database.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Workbook 1 - 20 of January
Item (identifier) Price Price2 Price 3
A 100 5 10
B 80 6 15
C 110 3 15
Workbook 2 - 21 of January
Item (identifier) Price Price2 Price 3
A 95 3 5
B 90 6 10
C 120 4 20
D 130 7 2
Workbook 3 - 22 of January
Item (identifier) Price Price2 Price 3
A 70 11 35
B 100 1 15
D 45 17 25</code>There are around 20 price columns, and thousands of items in each day (workbook). New identifiers might be added or removed in new days. My code gets this data and uses vlookup to match, as it adds new source files (if a file does not have an item that was there in the past, the information simply counts as zero).
My output in excel is:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Tab (Worksheet) 1 (price)
All the item Identifiers 20/01 21/01 22/01
A 100 95 70
B 80 90 100
C 3 4 -
D - 130 45
Tab (Worksheet) 2 (price2)
All the item Identifiers 20/01 21/01 22/01
A 5 3 11
B 6 6 1
C 110 120 -
D - 2 25</code>And so on.
First problem is how to compare the identifiers in each file, and add the new ones to the end of the output list (each time a new day workbook is included).
Second is that, just doing the current procedure, the code takes too long (more than 20 hours). If I transform this information (each file separately) to an array, how can I search the information inside the array, match it and past it to the output file? Is there a faster option than vlookup?
If not, is it possible to use it to get multiple results are once? (instead of running 1 lookup for each column of each identifier, run i lookup for each identifier only and return all the columns at once, since they are always the same)
Any help will be deeply appreciated.