Data extract
 

Greetings,

I need a date for specific product purchase. The data provided me was
arranged as follows:

Customer IDs in column A.
Subsequent columns are alternating between product ID and Date.

I only require the date of purchase for product K5, however each customer
may have that product listed in a different column from others.

Small example to illustrate; I apologize as the formatting will not appear
correct:

Customer ID Product Date Product Date Product Date
1234 A1 1/2/10 B4 12/1/09 K5
10/12/06
3324 K5 9/22/08 A1 3/14/07 B4
2/28/04
9845 B4 1/13/09 K5 6/29/03 A1
12/12/23


Thanks in advance for any suggestions!

Regards,
Brad

Data extract
 

Do you want the product K5 date for a specific customer?

--
Biff
Microsoft Excel MVP


"Brad Autry" wrote in message
...
Greetings,

I need a date for specific product purchase. The data provided me was
arranged as follows:

Customer IDs in column A.
Subsequent columns are alternating between product ID and Date.

I only require the date of purchase for product K5, however each customer
may have that product listed in a different column from others.

Small example to illustrate; I apologize as the formatting will not appear
correct:

Customer ID Product Date Product Date Product Date
1234 A1 1/2/10 B4 12/1/09 K5
10/12/06
3324 K5 9/22/08 A1 3/14/07 B4
2/28/04
9845 B4 1/13/09 K5 6/29/03 A1
12/12/23


Thanks in advance for any suggestions!

Regards,
Brad

Data extract
 

Hi
I am not sure what you want to accomplish, but try this
=COUNTIF(A1:H4,"K5") = counts number of occurencies in a range for "K5"
--
Click yes if helped
Greatly appreciated
Eva


"Brad Autry" wrote:

Greetings,

I need a date for specific product purchase. The data provided me was
arranged as follows:

Customer IDs in column A.
Subsequent columns are alternating between product ID and Date.

I only require the date of purchase for product K5, however each customer
may have that product listed in a different column from others.

Small example to illustrate; I apologize as the formatting will not appear
correct:

Customer ID Product Date Product Date Product Date
1234 A1 1/2/10 B4 12/1/09 K5
10/12/06
3324 K5 9/22/08 A1 3/14/07 B4
2/28/04
9845 B4 1/13/09 K5 6/29/03 A1
12/12/23


Thanks in advance for any suggestions!

Regards,
Brad

Data extract
 

Hi Eva,

Thanks for taking the time to look at my query.

What I want to accomplish is extract the date associated with product K5 for
each customer.

The data should be organized better, but it's not and this is what I have to
deal with.

I found a solution to what I'm trying to accomplish, although it's
definitely not the best. I concatenated every cell in the row, then did

=MID(AD2,FIND("K5",AD2),7)

to pull k5 and the date number from that string.

This is a shoddy way of going about it, though, so if someone has a better,
more proper way - I'd love to hear it.

Thanks again

"Eva" wrote:

Hi
I am not sure what you want to accomplish, but try this
=COUNTIF(A1:H4,"K5") = counts number of occurencies in a range for "K5"
--
Click yes if helped
Greatly appreciated
Eva


"Brad Autry" wrote:

Greetings,

I need a date for specific product purchase. The data provided me was
arranged as follows:

Customer IDs in column A.
Subsequent columns are alternating between product ID and Date.

I only require the date of purchase for product K5, however each customer
may have that product listed in a different column from others.

Small example to illustrate; I apologize as the formatting will not appear
correct:

Customer ID Product Date Product Date Product Date
1234 A1 1/2/10 B4 12/1/09 K5
10/12/06
3324 K5 9/22/08 A1 3/14/07 B4
2/28/04
9845 B4 1/13/09 K5 6/29/03 A1
12/12/23


Thanks in advance for any suggestions!

Regards,
Brad

Data extract
 

Your question is not entirely clear as to what you want, but let me take a
guess. Put this formula in Row 2 (I've assumed Row 1 is a header row) of an
unused column and copy it down...

=INDEX(A2:G2,1,1+SUMPRODUCT((A2:G2="K5")*COLUMN(A2 :G2)))

--
Rick (MVP - Excel)


"Brad Autry" wrote in message
...
Greetings,

I need a date for specific product purchase. The data provided me was
arranged as follows:

Customer IDs in column A.
Subsequent columns are alternating between product ID and Date.

I only require the date of purchase for product K5, however each customer
may have that product listed in a different column from others.

Small example to illustrate; I apologize as the formatting will not appear
correct:

Customer ID Product Date Product Date Product Date
1234 A1 1/2/10 B4 12/1/09 K5
10/12/06
3324 K5 9/22/08 A1 3/14/07 B4
2/28/04
9845 B4 1/13/09 K5 6/29/03 A1
12/12/23


Thanks in advance for any suggestions!

Regards,
Brad

Data extract
 

I want the date for every customer; if there is no K5 entry, I'd just write a
formula that says there's no K5 transaction.

I've another, similar scenario that I can not use "concatenate all cells
then search for product ID" method because all the fields in this second set
of data are numeric.

Thanks for the help!


"T. Valko" wrote:

Do you want the product K5 date for a specific customer?

--
Biff
Microsoft Excel MVP


"Brad Autry" wrote in message
...
Greetings,

I need a date for specific product purchase. The data provided me was
arranged as follows:

Customer IDs in column A.
Subsequent columns are alternating between product ID and Date.

I only require the date of purchase for product K5, however each customer
may have that product listed in a different column from others.

Small example to illustrate; I apologize as the formatting will not appear
correct:

Customer ID Product Date Product Date Product Date
1234 A1 1/2/10 B4 12/1/09 K5
10/12/06
3324 K5 9/22/08 A1 3/14/07 B4
2/28/04
9845 B4 1/13/09 K5 6/29/03 A1
12/12/23


Thanks in advance for any suggestions!

Regards,
Brad


.

Data extract
 

If occurencies are only once for each customer this is the formula
=INDEX($A2:$G2,1,MATCH("K5",$A2:$G2,0)+1)
change a range A2:to
and it will provide you the date of first K5 for each customer
copy it down
--
click yes if helped
Greatly appreciated
Eva


"Brad Autry" wrote:

I want the date for every customer; if there is no K5 entry, I'd just write a
formula that says there's no K5 transaction.

I've another, similar scenario that I can not use "concatenate all cells
then search for product ID" method because all the fields in this second set
of data are numeric.

Thanks for the help!


"T. Valko" wrote:

Do you want the product K5 date for a specific customer?

--
Biff
Microsoft Excel MVP


"Brad Autry" wrote in message
...
Greetings,

I need a date for specific product purchase. The data provided me was
arranged as follows:

Customer IDs in column A.
Subsequent columns are alternating between product ID and Date.

I only require the date of purchase for product K5, however each customer
may have that product listed in a different column from others.

Small example to illustrate; I apologize as the formatting will not appear
correct:

Customer ID Product Date Product Date Product Date
1234 A1 1/2/10 B4 12/1/09 K5
10/12/06
3324 K5 9/22/08 A1 3/14/07 B4
2/28/04
9845 B4 1/13/09 K5 6/29/03 A1
12/12/23


Thanks in advance for any suggestions!

Regards,
Brad


.

Data extract
 

Try this...

Data in the range A2:G4.

Enter this formula in I2:

=IF(COUNTIF(B2:G2,"K5"),INDEX(B2:G2,MATCH("K5",B2: G2,0)+1),"NA")

Format as Date

Copy down as needed

--
Biff
Microsoft Excel MVP


"Brad Autry" wrote in message
...
I want the date for every customer; if there is no K5 entry, I'd just write
a
formula that says there's no K5 transaction.

I've another, similar scenario that I can not use "concatenate all cells
then search for product ID" method because all the fields in this second
set
of data are numeric.

Thanks for the help!


"T. Valko" wrote:

Do you want the product K5 date for a specific customer?

--
Biff
Microsoft Excel MVP


"Brad Autry" wrote in message
...
Greetings,

I need a date for specific product purchase. The data provided me was
arranged as follows:

Customer IDs in column A.
Subsequent columns are alternating between product ID and Date.

I only require the date of purchase for product K5, however each
customer
may have that product listed in a different column from others.

Small example to illustrate; I apologize as the formatting will not
appear
correct:

Customer ID Product Date Product Date Product
Date
1234 A1 1/2/10 B4 12/1/09 K5
10/12/06
3324 K5 9/22/08 A1 3/14/07 B4
2/28/04
9845 B4 1/13/09 K5 6/29/03 A1
12/12/23


Thanks in advance for any suggestions!

Regards,
Brad


.

Data extract
 

You haven't responded to my other posting, so I don't know if you can see it
or not; however, here is a modification for it which does what you are now
asking for...

=IF(COUNTIF(A2:G2,"=K5")=0,"There is no K5
transaction",INDEX(A2:G2,1,1+SUMPRODUCT((A2:G2="K5 ")*COLUMN(A2:G2))))

--
Rick (MVP - Excel)


"Brad Autry" wrote in message
...
I want the date for every customer; if there is no K5 entry, I'd just write
a
formula that says there's no K5 transaction.

I've another, similar scenario that I can not use "concatenate all cells
then search for product ID" method because all the fields in this second
set
of data are numeric.

Thanks for the help!


"T. Valko" wrote:

Do you want the product K5 date for a specific customer?

--
Biff
Microsoft Excel MVP


"Brad Autry" wrote in message
...
Greetings,

I need a date for specific product purchase. The data provided me was
arranged as follows:

Customer IDs in column A.
Subsequent columns are alternating between product ID and Date.

I only require the date of purchase for product K5, however each
customer
may have that product listed in a different column from others.

Small example to illustrate; I apologize as the formatting will not
appear
correct:

Customer ID Product Date Product Date Product
Date
1234 A1 1/2/10 B4 12/1/09 K5
10/12/06
3324 K5 9/22/08 A1 3/14/07 B4
2/28/04
9845 B4 1/13/09 K5 6/29/03 A1
12/12/23


Thanks in advance for any suggestions!

Regards,
Brad


.

Data extract
 

Sorry, I forgot about the case where there is no K5 transaction
=IF(ISERROR(INDEX($A2:$G2,1,MATCH("K5",$A2:$G2,0)+ 1))=TRUE,"no K5
transaction",(INDEX($A2:$G2,1,MATCH("K5",$A2:$G2,0 )+1)))
Copy this formula down
Click yes if helped
Greatly appreciated
Eva


"Eva" wrote:

If occurencies are only once for each customer this is the formula
=INDEX($A2:$G2,1,MATCH("K5",$A2:$G2,0)+1)
change a range A2:to
and it will provide you the date of first K5 for each customer
copy it down
--
click yes if helped
Greatly appreciated
Eva


"Brad Autry" wrote:

I want the date for every customer; if there is no K5 entry, I'd just write a
formula that says there's no K5 transaction.

I've another, similar scenario that I can not use "concatenate all cells
then search for product ID" method because all the fields in this second set
of data are numeric.

Thanks for the help!


"T. Valko" wrote:

Do you want the product K5 date for a specific customer?

--
Biff
Microsoft Excel MVP


"Brad Autry" wrote in message
...
Greetings,

I need a date for specific product purchase. The data provided me was
arranged as follows:

Customer IDs in column A.
Subsequent columns are alternating between product ID and Date.

I only require the date of purchase for product K5, however each customer
may have that product listed in a different column from others.

Small example to illustrate; I apologize as the formatting will not appear
correct:

Customer ID Product Date Product Date Product Date
1234 A1 1/2/10 B4 12/1/09 K5
10/12/06
3324 K5 9/22/08 A1 3/14/07 B4
2/28/04
9845 B4 1/13/09 K5 6/29/03 A1
12/12/23


Thanks in advance for any suggestions!

Regards,
Brad


.

Data extract
 

It's better to mirror ur data to a new table with Product and Date in
respectively single column instead of three

Assume original data is in Sheet1, In a new sheet,
A1:C1 will be the header, Customer ID, Product, Date
A2 =IF(OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/3),0)="","",OFFSET(Sheet1!
$A$2,INT((ROWS($1:1)-1)/3),0))
B2:C2 =IF(OFFSET(Sheet1!$B$2:$C$2,INT((ROWS($1:1)-1)/3),2*MOD(ROWS
($1:1)-1,3))="","",OFFSET(Sheet1!$B$2:$C$2,INT((ROWS($1:1 )-1)/3),2*MOD
(ROWS($1:1)-1,3))) <--Ctrl+Shift+Enter (Array Formula)
*note: highlight B2:C2 before paste in the array formula
*copy A2:C2 as far down as u need

Now, u have a well consolidated table, with Product and Date split
from 1 row to 3 rows
Apply auto filter on this table, u can filter by product easier
through dropdown list, and u can even sort and/or filter the data
anyhow u like. this should comes in handy. Hope my suggestion helps.