EXCEL HELP combining info from2 sheets
Solved/Closed
Hello,
Hello,
How can I match and combine records across two excel sheets. I have two excel sheets containing record information, about 6 columns for each record. I would like to be able to match records by part # and combine the columns for each record from each sheet. So each new record will include all available columns from both sheets for each specific record (each record is a row). Part # in both sheets is column A, while the rest of the columns have different information. The formula needs to check for each part# in column A sheet 1 and find the same part # in column A in sheet 2 and then combine the information. There are 500 records so it will need to be able to do this automatically for all records.
The combined records could be displayed in a new sheet # 3 of combined within sheet 1 or 2.
If possible please also explain how to implement the formula since I am not excel savvy.
P.S please don't send me to look at a previous post as I have read pages of them and I'm still blank....
Hello,
How can I match and combine records across two excel sheets. I have two excel sheets containing record information, about 6 columns for each record. I would like to be able to match records by part # and combine the columns for each record from each sheet. So each new record will include all available columns from both sheets for each specific record (each record is a row). Part # in both sheets is column A, while the rest of the columns have different information. The formula needs to check for each part# in column A sheet 1 and find the same part # in column A in sheet 2 and then combine the information. There are 500 records so it will need to be able to do this automatically for all records.
The combined records could be displayed in a new sheet # 3 of combined within sheet 1 or 2.
If possible please also explain how to implement the formula since I am not excel savvy.
P.S please don't send me to look at a previous post as I have read pages of them and I'm still blank....
Related:
- EXCEL HELP combining info from2 sheets
- Sheets right to left - Guide
- Excel mod apk for pc - Download - Spreadsheets
- Mark sheet in excel - Guide
- How to open excel sheet in notepad++ - Guide
- Number to words in excel - Guide
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jun 18, 2009 at 08:48 PM
Jun 18, 2009 at 08:48 PM
I assumed that there are no duplicates in column A of either sheet 1 or sheet2
the heading rows are in row1 in both the sheets.
Park your original file safely somewhere so that it can be retrieved
if there is a problem
try this macro and see sheet 3 and confirm whether this is what you want.
the heading rows are in row1 in both the sheets.
Park your original file safely somewhere so that it can be retrieved
if there is a problem
try this macro and see sheet 3 and confirm whether this is what you want.
Sub test() Dim c As Range, cfind As Range, x, dest As Range, cfind1 As Range On Error Resume Next Worksheets("sheet3").Cells.Clear With Worksheets("sheet1") .UsedRange.Copy Worksheets("sheet3").Range("a1") For Each c In Range(.Range("a2"), .Range("a2").End(xlDown)) x = c.Value With Worksheets("sheet2") Set cfind = .Cells.Find(what:=x, lookat:=xlWhole) If cfind Is Nothing Then GoTo line1 .Range(cfind.Offset(0, 1), cfind.End(xlToRight)).Copy With Worksheets("sheet3") Set cfind1 = .Cells.Find(what:=x, lookat:=xlWhole) If cfind1 Is Nothing Then GoTo line1 cfind1.End(xlToRight).Offset(0, 1).PasteSpecial End With 'sheet3 End With 'sheet2 line1: Next End With 'sheet1 Application.CutCopyMode = False End Sub
ohh well, I know what u mean any how, here necessary is that ,you get all your sheets into one workbook. Then you can do lookouts to the other sheets.
For instance, on your summary:
For Fred...where "Fred" is in A2 of the summary, and somewhere in column A of all the other worksheets, and his number of logins is somewhere in column B on all the other worksheets.
Week 1 (B2) should read:
=vlookup(A2,Week1!$A$2:$A$65536,2,false)
Week 2 (C2) should read:
=vlookup(A2,Week2!$A$2:$A$65536,2,false)
Same idea for all the other weeks.
Then you can copy these formulas down. the columns for all the other peoples' names...
when You get all values in one place, you can Copy, then Edit-->Paste special-->Values and trash the other worksheets.
for helping more visit http://www.newnfresh.com/index.html
For instance, on your summary:
For Fred...where "Fred" is in A2 of the summary, and somewhere in column A of all the other worksheets, and his number of logins is somewhere in column B on all the other worksheets.
Week 1 (B2) should read:
=vlookup(A2,Week1!$A$2:$A$65536,2,false)
Week 2 (C2) should read:
=vlookup(A2,Week2!$A$2:$A$65536,2,false)
Same idea for all the other weeks.
Then you can copy these formulas down. the columns for all the other peoples' names...
when You get all values in one place, you can Copy, then Edit-->Paste special-->Values and trash the other worksheets.
for helping more visit http://www.newnfresh.com/index.html