Lookup, Match or simple formula?

  • Thread starter Thread starter Jeff Smith
  • Start date Start date
J

Jeff Smith

Hi,

I have a table with date (Col B) and items productID (Cols C:I) and want to
analyse frequency of purchases by product.
In the analysis section I have a column for every product and only want to
put the date (from column B) if the product appears in that line.

E.g
B C D E F G H I
30/7/04 101 231 301 365 389 401 421

For say product "101" appearing in colum C, I want the formula to show the
date (column B, that row) in the analysis column (in Col M), similarly the
same date will show in column AA for product "231" etc.

I would then copy the formula across and down.

i.e IF(Product in header column M ocurrs in the range C:D, Column B date,"")

This is beyond my skils and I wonder if someone can help?

TIA

Jeff Smith
 
One way:

Assuming your data as posted is in B1:I1

Put in M1: 101

Put in M2: =IF(COUNTIF($C1:$I1,M$1)<>0,$B1,"")
Format M2 as date

If M1:AA2 houses the various product IDs,
and rows2, 3, 4 etc in cols B to I
contain data similar to that in row1 (as posted):

Copy M2 across to AA2,
then fill down as many rows as there is data in cols B to I
 
Max, this worked a treat. Many thanks.

Jeff

Max said:
One way:

Assuming your data as posted is in B1:I1

Put in M1: 101

Put in M2: =IF(COUNTIF($C1:$I1,M$1)<>0,$B1,"")
Format M2 as date

If M1:AA2 houses the various product IDs,
and rows2, 3, 4 etc in cols B to I
contain data similar to that in row1 (as posted):

Copy M2 across to AA2,
then fill down as many rows as there is data in cols B to I
Click to expand...
 
Back
Top