Data extract

  • Thread starter Thread starter Brad Autry
  • Start date Start date
B

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
 
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"
 
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
 
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)))
 
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!
 
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
 
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
 
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))))
 
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
 
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.
 
Back
Top