combine, merge, consolidate worksheets

Originally Posted by biglu

I have multiple worksheets with all the same headings.
Name:Title:Dept:ComSvrName:ComSvrMonth:ComSrvYr

Each worksheet can have different total number of rows.
W1
Jay:Director:Math:helping:8:2006
Alex:Cord:Sci:Dissecting:2:2003
Ray:Asst:Art:Drawing stuff:4:2005
Debra:Asst:Health:driving car:2:2002

W2
Jay:Director:Math:teaching students:7:2001
Alex:Cord:Sci:universal studies:5:2005
Ray:Asst:Art:dancing:1:2004

W3
Jay:Director:Math:times table:4:2002

How can I combine all the worksheets so we get a new worksheet with just 1 row for each person's information?
The cell names would look like
Name:Title:Dept:ComSvrName:ComSvrMonth:ComSrvYr:ComSvrName2:ComSvrMonth2:ComSrvYr2:ComSvrName3:ComSvrMonth3:ComSrvYr3

NOTE: Although there are a different number of total rows in each worksheet the person's name, title, dept do not change. One the comsrv info changes.

Any help with this would be super great!
Thanks,

Hi,

If 'Name' is unique, probably start in W1, re-arrange columns as required (insert new columns, drag the current columns to new positions etc) then VLookup the required details from other Sheets.

Copy those columns and Paste Special = Values to retain the information.

hth
---

Originally Posted by biglu

Are you saying I should add more colunms in W1, copy and paste the info. from the other worksheets into these new columns and then do VLookUP to match up the name?

What you wrote sounds like it would work, but I dont follow you all the way through and I've only used VLookUp once so I need more help there. You lost me right after "start in W1"...

Can you please give more explanation so I can try it?

Thanks,
LU

Hi,

from your original, W1 appeared to be the major data source, having a (seemingly) full list of names and the first few columns required.

Put headings on the following columns as required, and then:

assuming that W2 and W3 do not have the same data (just the same names with different data)

in W1, the 'next' column (G ? ) ComSvrName2

in G2 put

=If(IsError(VLookup(A2,W2!A:F,4,False)),"",=VLookup(A2,W2!A:F,4,False))

in H2 ComSvrMonth2
=If(IsError(VLookup(A2,W2!A:F,5,False)),"",=VLookup(A2,W2!A:F,5,False))

in I2 ComSrvYr2
=If(IsError(VLookup(A2,W2!A:F,6,False)),"",=VLookup(A2,W2!A:F,6,False))

in J2 - L2 ComSvrName3:ComSv rMonth3:ComSrvYr3

=If(IsError(VLookup(A2,W3!A:F,4,False)),"",=VLookup(A2,W3!A:F,4,False))
=If(IsError(VLookup(A2,W3!A:F,5,False)),"",=VLookup(A2,W3!A:F,5,False))
=If(IsError(VLookup(A2,W3!A:F,6,False)),"",=VLookup(A2,W3!A:F,6,False))

Select G2:L2 and formula fill down as far as your data goes

After that, to retain this data, select columns G:L and Copy
then Paste Special = Values back over itsself.

Let me know how you go.

---
formula fill is described at http://www.mvps.org/dmcritchie/excel/fillhand.htm

Originally Posted by biglu

I removed the spaces in the code, but it didn't work so I included a sample excel file zipped up so you can see the error.

Thanks,
LU

Hi,

There is no error in the .zip, nor any lookups

Is this the correct file?


aaha - - the second = sign needs to be removed from the formula ,
and the Sheet names are Sheet2 not W2 and Sheet3 not W3
---

Originally Posted by biglu

I removed the spaces in the code, but it didn't work so I included a sample excel file zipped up so you can see the error.

Thanks,
LU

fixed in the attached

---