TIME CRITICAL PROBLEM.

  • Thread starter Thread starter Maly
  • Start date Start date
M

Maly

Hi

Sorry about the length of this request but I feel it may
be necessary to try to show what I would like to happen.

I have a workbook with 6 worksheets.

The sheets are called Nov 01, Feb 02, May 02, Aug 02, Nov
02 and Jan 03.

Each sheet contains ID, name, address and loan details for
individual clients. Some clients have loans on all month
sheets and some may only have loans for a particular month
sheet.

What I need to happen is to conslidate all the loans onto
one sheet but only show the clients name and address
details once with all loans being in individual columns.

Below are short examples of how the spreadsheets look at
present and how I would like the consolidated one to
appear.

FEB 02

EMPL ID NAME_1 NAME_2 ADDRESS_1 LOAN
3257 Fred Bloggs Somewhere 1563521
3260 Bill Who Elsewhere 1533864
3275 Jim Why Here 356425

MAY 02

EMPL_ID NAME_1 NAME_2 ADDRESS_1 LOAN
3257 Fred Bloggs Somewhere 2659651
3258 Sally How Nowhere 1526892
3260 Bill Who Elsewhere 859652


CONSOLIDATED

EMPL_ID NAME_1 NAME_2 ADDRESS_1 FEB 02 MAY 02
3257 Fred Bloggs Somwhere 1563521 2659651
3258 Sally How Nowhere - 1526892
3260 Bill Who Elsewhere 1533864 859652
3275 Jim Why Here 356425 -


This is just a quick example to give an idea. Obviously,
I have six worksheets that need this work with a lot more
information in them.

The earliest sheet (Nov 01) has 76 clients and the latest
(Jan 03) has 350.

Any assistance would be greatly appreciated.

Should you wish further information, please feel free to
contact me by email.

Many thanks

Malcolm Davidson
 
There are so many ways you could probable do this but this
is one way which i think is pretty quick - it may not be
in the format you require and others may have better ideas

I would copy all the records into one sheet, making sure
to add a new column, "Month".

Then simply run a pivot table over the data with
the "Month" column along a row axis

Month EMPL ID NAME_1 NAME_2 ADDRESS_1 LOAN
Jan 3257 Fred Bloggs Somewhere 1,563,521
Feb 3260 Bill Who Elsewhere 1,533,864
Feb 3275 Jim Why Here 356,425
Mar 3275 Jim Why Here 254,125
 
I like Kevin's much better than this, but another way:

Consolidate the employee id's (and names/addresses) into a new worksheet. Make
sure you don't miss any. Copy from each of the detail sheets, then sort that
giant list. Then delete duplicate id's.

If the id's were in A1:A9999, then use a helper column and a formula like:
=IF(COUNTIF($A$1:A1,A1)=1,"Keep","duplicate")
And drag down.

Only the first id will be kept. Chip Pearson has some other techniques at:
http://www.cpearson.com/excel/duplicat.htm

Now you can do a series of =vlookup()s for each of the date columns.

=if(iserror(vlookup($a1,'Nov 01'!$a$1:$E$9999,5,false)),0,
vlookup($a1,'nov 01'!$a$1:$e$9999,5,false))

(all one cell)
and copy down, then copy to the right, adjust the sheet name and drag down.
(and so forth).

=====

But I'd use Kevin's method. There's a lot more stuff you can do with the data
laid out nicely like he suggested.
 
Back
Top