Excel/Word merge problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created an excel spreadsheet using our customer management system{I work in insurance}. In this excel spreadsheet it brought over all the clients that I wanted.

The problem is any client which has more than 1 vehicle attached has made a duplicate line on excel, basically you will see that person listed multiple times depending on how many vehicles they have. Then each line will have a different vehicle.

Unfortunately this is the only way I can create this excel spreadsheet thru our management system.

I am trying to merge this info with Word. I would like to add all vehicles for each client added to 1 document.

Does anyone know how I can do this? I'm not sure if I can merge fields in excel or somehow use some type of word field in Microsoft word.

I am using Office XP.

Please help if you can.
 
For example in excel it is showing as follows

Column1 Column2 Column3

John Smith Auto1
John Smith Auto2
John Smith Auto
David Hoth Auto1
Craig Jones Auto
Craig Jones Auto

And what I want to have in Excel is this:

Column1 Column2 Column3

John Smith Auto1 Auto2 Auto3
David Hoth Auto
Craig Smith Auto1 Auto2
 
Yes this can be done. A couple things that would be
helpful to know like what's the most vehicles a client can
have?

Anyway, the gist is...

1) use a copy of the spreadsheet (work a backup not the
original)
2) Assuming a SORTED list and assuming your example has a
header row (first,last, auto1,auto2,auto3,auto4,auto5) in
row 1
3) in D2 put formula =IF((A2=A3)*(B2=B3),C3,"")
4) in E2 put formula =IF((D2<>"")*(A3=A4)*(B3=B4),C4,"")
5) in F2 put formula =IF((E2<>"")*(A4=A5)*(B4=B5),C5,"")
6) in G2 put formula =IF((F2<>"")*(A5=A6)*(B5=B6),C5,"")
Note: The pattern can be extended -- this handles up to 5
vehicles.
7)Select D2 thru G2 and autofill down the length of your
data which should populate or not out to column G.
8) Next set an advanced filter (Dat->Filter->Advanced
Filter)on columns A & B with criteria being columns A & B
and check on "Unique records Only" Excel will keep the
first instance and filter away all dupes below it. Since
the formula percolated all the autos to the first unique
name, all should look pretty good at this point.
9) Next Select all the visible data that will be going
into your merge and after selected press [Alt]+[;] which
will grab just the visible cells. Now you can copy and
paste to a new worksheet or workbook.
10) Save the new goodstuff and proceed with your mailmerge.

Remember this all assumes good sorted data. Clear as mud?
There are other ways to do this (using offset() for
example) but they often look even less intuitive.

Regards,

Kent
Excel Support Technician
www.canhelpyou.com
 
Back
Top