Doing a data look up

Ashan Ranasinghe · #1 04-04-2011, 08:46 PM

Hi,

I'm new to this forum and so I'm not sure if this is the right place to post this but i need help.

my problem is i have a few excel documents lets call them

Jan.xlsx
Feb.xlsx
March.xlsx

all of them will have 3 columns
index
customer name
payment amount

now what i want to do is have another excel file called
revenue2011.xlsx

this will have the columns
Index
Jan
Feb
March

the data in the revenue2011.xlsx should be taken from the Jan, Feb and March files without duplicating the index numbers. the data that will be extracted are the "index" and "payment amount"

is there a procedure or formula i can use that will do this.

thanks again

macropod · #2 04-09-2011, 10:41 PM

Hi Ashan

If your 'Index' values are stored as a master list in the summary workbook, you could use a LOOKUP formula to retrieve to values from each of the other workbooks.

If your 'Index' values are not stored as a master list in the summary workbook, you would need a macro-based solution that would open each source workbook, check its index entries against those that might (not) already be in the summary workbook and if, not found in the summary workbook, add the index entry. You can then use the formula approach mentioned above, or have your macro copy the value to the relevant column for the index row.

As you can see from the above, one possible solution is fairly simple and can use just formulae; the other is much more complex and requires a macro for at least part of the process.

Matthew · #3 04-11-2011, 10:57 AM

Hi
As Paul said it all depends on what the 'index' contains.
eg if it is a simple number for each month 1 through to 9999 then there is a way of doing it, not to complex.
If it contains a date ref even better eg 01-03-11/1

Either way it is all very doable and would not necessarily need a macro.

May be an idea to post an example ?

Regards
Matthew

OTPM · #4 05-04-2011, 05:26 AM

Quote:

Originally Posted by Ashan Ranasinghe

Hi,

I'm new to this forum and so I'm not sure if this is the right place to post this but i need help.

my problem is i have a few excel documents lets call them
Jan.xlsx
Feb.xlsx
March.xlsx

all of them will have 3 columns
index
customer name
payment amount

now what i want to do is have another excel file called
revenue2011.xlsx

this will have the columns
Index
Jan
Feb
March

the data in the revenue2011.xlsx should be taken from the Jan, Feb and March files without duplicating the index numbers. the data that will be extracted are the "index" and "payment amount"

is there a procedure or formula i can use that will do this.

thanks again

Hi
Please see examples attached. You will need to change the filepaths in the formulae in the Revenue spreadsheet to suit your own file structure.
Hope this helps.
OTPM