EXCEL HELP combining info from2 sheetsSolved/Closed

Question

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....

venkat1926 · Accepted Answer

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. 

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

coupen66 · Answer

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

EXCEL HELP combining info from2 sheets

2 responses

Similar discussions

Subscribe To Our Newsletter!