Not even sure where to start or search. Did this ever match that?

[type88]

Hey guys, you have been a big help to me in the past but I usually know where to begin explaining myself. I will try, and I hope it makes sense.

The real life situation is like this:
I have a spreadsheet that lists each and every item sold, and it includes the customer information. The items are not really important, but the guest information is.

A: Customer Number
B: Purchasing Method (online, storefront, mail order) these are listed as 2A, 2B, 2C

Because we want to measure how much of our online business came from us via customers that originally shopped in our storefront, I would like to create a column C which answers the question:

<i>Was this customer ever a storefront customer?</i>

The formula would of course have to search down the list for the customer number in column A and determine if the value in column B was ever "2B".

Thank you for your help and sharing.

 

[sheetspread]

Excel Workbook
	A	B	C
1	Customer Number	Purchasing Method	Storefront?
2	661437	2A
3	684474	2B	Yes
4	662241	2C
5	671265	2B	Yes
6	657784	2C
7	671265	2B	Yes
8	662241	2A
9	671265	2C	Yes
10	684474	2C	Yes
11	662241	2A
12	671265	2A	Yes
13	662241	2A
14	671265	2B	Yes
15	662241	2A
16	671265	2C	Yes
17	657784	2A
18	671265	2A	Yes
19	662241	2C
20	671265	2A	Yes
21	684474	2B	Yes
22	662241	2A
23	671265	2C	Yes
24	671265	2C	Yes
25	661437	2C
Sheet3

 

[Michael M]

Hi
I might be missing something, but couldn't you simply use in "C"

=IF(B1="2B", "Y","")

and copy down ?

 

[type88]

sheetspread: this worked like a charm. with 40k plus rows it is a bit slow, but it gets the job done nicely, thank you very much. now i have another function to investigate, SUMPRODUCT.

michael m: you were missing something, though i appreciate your input none-the-less. what i need to determine is if the value in B was ever 2B for a given customer.

thanks a million.

 

[sheetspread]

You've really hit the threshold using Excel as a database (like so many others who just want to "jump in" rather than spend time learning another program). I suggest MS Access.

 

[shg]

You could autofilter the second column for 2B, or use a pivot table.

 

[type88]

I realize that I am pushing excel to the limit, but the document is automatically created from the sales program that we use. No doubt it will grow even more as this only represents 5 months of sales.

It will only ever be used to perform this math after which time it will get dumped into a BI program (Tableau).

Thanks again!