help merging to tables into one

I

ittop

New member
Joined
Jan 13, 2012
Messages
6
Reaction score
0
Points
0
i have a set of data
now i want excel to look up value 12345 from table 1 search table 2
and return with entire column
thus now making a table with 5 cloumns
and matched correspondingly how do i do that1 want
123451/2/20114/2/2011
125672/25/20113/20/2011
12573/20/20114/20/2011

123451/23/2011
125672/25/2011
34563/5/2011
 
Hi there,

Sorry for the late reply, and not sure if you still need help with this. if you do, it might be helpful to upload a sample workbook for us. (Just click Go Advanced, and you can do so.)
 
here is a sample i have to combine sheet 1 and 2
 

Attachments

  • sample.xlsx
    10.2 KB · Views: 23
what ever method i use it looks up the first column of data and ignores the rest
 
I need a clarification on this, as per your sheet, you want to lookup the values in Column A which would return the values in the entire Corresponding row is it ?? Can you please describe this better ?
 
Yes i want to merge all values corresponding to A into single column
 
I'm still not quite following this either.

Can you product a sheet that shows us what the expected output will look like from what you've already given us?
 
sure this is the way i want the result
i have a pivot table of sheet 2 can i merge it with normal table of sheet 1
or can i merge 2 pivot table
 

Attachments

  • sample.xlsx
    10.8 KB · Views: 17
Sorry, so in the example file, you have sheet 1 and sheet 2... you want to copy all the data from sheet 2 and append it to the data table in sheet 1, is that correct?

Sheet 2's data is in a pivot table... is sheet 1's data as well, or is it just a range of data, and not a formal table?
 
yes ur right sheet 2 is a pivot table sheet 1 is a normal table
 
Based on what you have in the file you provided, I think this may work for you.

You'll need to:
  • Open the Visual Basic editor (press Alt+F11)
  • Navigate to your project
  • Right click your project and choose Insert->Module
  • Paste the code below in the module:
Code: 
Sub Consolidate()
    With Worksheets("Sheet2")
        .Range("A2:" & .Range("A2").End(xlToRight).End(xlDown).Address).Copy
    End With
    
    With Worksheets("Sheet1")
        .Range("A1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        .Columns("B:C").NumberFormat = "m/d/yy h:mm AM/PM;@"
    End With
    
End Sub

Next:
  • Close the Visual Basic editor
  • Save the file
  • Press Alt+F8 to run the macro
  • Choose "Consolidate"
Hopefully it works for you, but let me know if not. One thing you need to be aware of here is that this will copy from "Sheet2" to "Sheet1", and assumes you already have at least two rows of data in Sheet1. If that's not the case, it will probably fail.
 
You must log in or register to reply here.
Back
Top