I have 4 worksheets that need to be consolidate into one to form a master
listing, there are known duplicates between worksheets - total ~30000 records
and 30 colums

name hours date project
x 1 10/01/2005 a
x 2 11/01/2005 a
y 1 12/01/2005 b

I then need to compare this master to 4 other worksheets to determine in
which sheet the record is located. Any easy way to do this ?

Tried CONCETENATE and VLOOKUP - keeps crashing :(

My advice is to use MS Access - import the four sets of data into a single
table, then run a Make Table query against that table and ask it to return
unique records.

After you have the unique records in a new table, link in the 4 other
worksheets and run your queries there.

Make no mistake - you CAN do this in Excel, but it's the wrong tool for this
kind of job. A little like trying to drive a nail with the handle on a
screwdriver.

"triniti" wrote:

I have 4 worksheets that need to be consolidate into one to form a master
listing, there are known duplicates between worksheets - total ~30000 records
and 30 colums

name hours date project
x 1 10/01/2005 a
x 2 11/01/2005 a
y 1 12/01/2005 b

I then need to compare this master to 4 other worksheets to determine in
which sheet the record is located. Any easy way to do this ?

Tried CONCETENATE and VLOOKUP - keeps crashing :(

Im using this for billing for a monster project that is nearing completion.

The information is timesheets extracted from SAP on four dates covering
different date ranges - some of the date ranges overlap, there is also
additional information that has been added to the SAP extract by my
predessecor that needs to be retained. (Unfortunately - I dont know squat
about SAP, and neither do the rest of my team - so we can only work on the
xls we are provided).

I have the duplicates sorted out ... there werent as many as initially
suspected ... the problem now is ... The substantation for some of the
Statements of work (supposedly based on actuals) has already been provided
.... so I need to compare this master list to the information provided already
as part of the Statements of work to confirm what each has been billed
against and what is left over that remains to be billed.

I know a little about access ... enough to navigate around an existing
database .. but not how to setup a new one and construct the relevant
queries.

I now have master.xls, SOW1.xls, SOW2.xls, SOW3.xls, SOW4.xls. I need
something to do the following ... i know it is similar to the first question
... which is why i posted it first.

IF (master!a1:AB1) is in (SOW1!A1:AB20000) THEN AC1=SOW1 ELSE
IF (master!a1:AB1) is in (SOW2!A1:AB20000) THEN AC1=SOW2 ELSE
IF (master!a1:AB1) is in (SOW3!A1:AB20000) THEN AC1=SOW3 ELSE
IF (master!a1:AB1) is in (SOW4!A1:AB20000) THEN AC1=SOW4 ELSE



"Duke Carey" wrote:

My advice is to use MS Access - import the four sets of data into a single
table, then run a Make Table query against that table and ask it to return
unique records.

After you have the unique records in a new table, link in the 4 other
worksheets and run your queries there.

Make no mistake - you CAN do this in Excel, but it's the wrong tool for this
kind of job. A little like trying to drive a nail with the handle on a
screwdriver.

"triniti" wrote:

I have 4 worksheets that need to be consolidate into one to form a master
listing, there are known duplicates between worksheets - total ~30000 records
and 30 colums

name hours date project
x 1 10/01/2005 a
x 2 11/01/2005 a
y 1 12/01/2005 b

I then need to compare this master to 4 other worksheets to determine in
which sheet the record is located. Any easy way to do this ?

Tried CONCETENATE and VLOOKUP - keeps crashing :(