Data extract

[Brad Autry]

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

 

[T. Valko]

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

 

[Eva]

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"

 

[Brad Autry]

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

 

[Rick Rothstein]

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)))

 

[Brad Autry]

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!

 

[Eva]

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

 

[T. Valko]

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

 

[Rick Rothstein]

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))))

 

[Eva]

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

 

[minyeh]

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.