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?

Look in the help index for VLOOKUP. Or, use a FIND macro.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gmoore" wrote in message
...
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?

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
---
"gmoore" wrote:
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?

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?

"Max" wrote:

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
---
"gmoore" wrote:
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?

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(Sheet 2!B:B,MATCH(D2&"",Sheet2!A:A,0)))

voila?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"gmoore" wrote:
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?

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

"Max" wrote:

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(Sheet 2!B:B,MATCH(D2&"",Sheet2!A:A,0)))

voila?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"gmoore" wrote:
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?

Lost you there. Can you paste some sample data, the cust ids in Sheet2's col
A, and the cust ids in Sheet1's D2 down?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"gmoore" wrote in message
...
The first two cells in column E merged together with this formula.....????
Still no data.

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 ..
"Max" wrote:

Lost you there. Can you paste some sample data, the cust ids in Sheet2's col
A, and the cust ids in Sheet1's D2 down?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"gmoore" wrote in message
...
The first two cells in column E merged together with this formula.....????
Still no data.

All of the id's in sheet 2 are not in sheet 1 ..
If that's the case, then of course there'll be no matching results to
return?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

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?
"Max" wrote:

All of the id's in sheet 2 are not in sheet 1 ..
If that's the case, then of course there'll be no matching results to
return?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

Of course, that's the objective you posted
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"gmoore" wrote in message
...
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?

Then what is the next step to solve the problem?

"Max" wrote:

Of course, that's the objective you posted
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"gmoore" wrote in message
...
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?

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)),"",IN DEX(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
---
"gmoore" wrote:
Then what is the next step to solve the problem?

Typo, it should be "In E2, copied down: .."
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

Still nothing....

"Max" wrote:

Typo, it should be "In E2, copied down: .."
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

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
---
"gmoore" wrote in message
...
Still nothing....

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gmoore" wrote in message
...
Still nothing....

"Max" wrote:

Typo, it should be "In E2, copied down: .."
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gmoore" wrote in message
...
Still nothing....

"Max" wrote:

Typo, it should be "In E2, copied down: .."
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

Don,
Thanks for posting back here, and sharing your resolution to the OP. Great
stuff.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Don Guillett" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software