Combining Data from 2 worksheets

G

gmoore

I have two worksheets, one has the customer's name and address, customer ID,
etc. The other contains the customer's ID and last date of purchase. How can
I combine the two?
 
M

Max

One easy, generic option is index/match. This should get you going strongly ..

I'd assume you want to pull over the last date of purchase from Sheet2 via
matching the cust id (unique key). Assume Sheet2 contains cust ids/last date
of purchase running in A2:B2 down

In Sheet1,
Assume cust ids in col D, running in D2 down
In E2:
=IF(ISNA(MATCH(D2,Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(D2,Sheet2!A:A,0)))
Format as date to taste, copy down to return the desired last date of
purchase. Non-matching cases (if any) will return blanks: "". This part:
INDEX(Sheet2!B:B .. is what you want returned as the results. It can be a col
to the left or right of the match col. Easily adjust the expression to suit
your actual data / sheetname set-up. Cross-apply it elsewhere to do likewise
with consumate ease. Success? Thump the air, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
G

gmoore

To be on the safe side I set up the sheet exactly as you explained
below....still no luck. No data was returned just the green triangle in the
top left corner. I cut and pasted the formula to make sure there were no
errors in my typing.
Would the formating have anything to do with why it's not returning the data?
 
M

Max

Your data is inconsistent, probably the cust ids in Sheet2's col A are text
nums while your lookup values in D2 down are real nums.

Try this slightly revised, the &"" bit will convert the lookup values in D2
down to text numbers for consistent matching
=IF(ISNA(MATCH(D2&"",Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(D2&"",Sheet2!A:A,0)))

voila?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
G

gmoore

The first two cells in column E merged together with this formula.....????
Still no data.
 
G

gmoore

Sheet 1 :
025
026
027
030
030
031
035
036

Sheet 2
Customer ID
303
18724
26849
50900
76740
079
10787
11400
12923
1300

All of the id's in sheet 2 are not in sheet 1 ..
 
G

gmoore

I'm sorry....I meant that only the customer ID's that have a date of sale are
in sheet 2. All of the customers are listed in sheet 1 but only the customer
with a last date of sale are in sheet two. Should it return these?
 
M

Max

Since it appears that there might be a leading zeros issue, try this 2nd
revision, the TEXT bit will help improve it for consistent matching. In D2,
copied down
=IF(ISNA(MATCH(TEXT(D2,"000"),Sheet2!A:A,0)),"",INDEX(Sheet2!B:B,MATCH(TEXT(D2,"000"),Sheet2!A:A,0)))
Let me know here how it goes with you
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
M

Max

Then your data is probably too inconsistent for generic matching to happen.
I'm out here. Suggest you start a brand new thread, post a good
representative spread of samples of your data. Good luck.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
D

Don Guillett

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.
 
D

Don Guillett

Provided to OP. Problem was formatting and a need to TRIM
Sub TrimAndVerifyDatesSAS()
Application.Calculation = xlCalculationManual
With Sheets("Customer ID and Date")
.Columns(1).NumberFormat = "@"
lr = .Cells(Rows.Count, 1).End(xlUp).Row
For Each c In .Range("a2:a" & lr)
c.Value = Application.Trim(c)
Next c
dlr = Sheets("Mailing List").Cells(Rows.Count, "d").End(xlUp).Row
MsgBox dlr
For Each c In Sheets("Mailing List").Range("d24:d" & dlr)
'MsgBox c
Set mf = .Columns(1).Find(What:=c, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not mf Is Nothing Then
'MsgBox mf.Row
'MsgBox mf.Offset(, 1)
c.Offset(, 1) = Format(mf.Offset(, 1), "mm/dd/yy")

End If
Next c

End With
Application.Calculation = xlCalculationAutomatic
MsgBox "done"
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

matching & combining data from seperate worksheets 2
Combine data from 2 worksheets using unique identifier 1
Lists 4
How to return data? 6
Linking worksheets 1
Match & combine rows from 2 worksheets 6
How to fill out a table (a row) with data from another worksheet 4
Combining an IF with another formula 4

Top