Matching dates to cells


D

Donna

Hi,
IS THERE A WAY TO FIND ALL CORRESPONDING A'S FOR COLUMN "F" THAT HAVE
THE SAME DATE IN COLUMN A. WHAT I WOULD LIKE FOR IT TO DO IS IN ANOTHER
COLUMN TO NUMBER THE ADJUSTMENTS (COL G).
Col A Col B Col C Col D Col E Col F COL G
1/8/2008 2396112 50 NEW C
1/17/2008 25391 -50 R
1/28/2008 2406382 50 REN C
2/7/2008 25572 -50 A 1
7/22/2009 2716889 14,268.00 REN C
7/22/2009 -10,701.00 F
7/29/2009 1387 -3,567.00 R
7/22/2009 2716889F 10,701.00 REN F
8/12/2009 ach081209 -10,701.00 C
4/28/2008 26513 -1,136.00 R
4/21/2008 2453844 19 END C
4/28/2008 26596 -19 R
5/30/2008 2476656 1,155.00 INS C
6/24/2008 -1,155.00 V
6/24/2008 2490192 531 END C
8/1/2008 26784 -12 R
8/5/2008 -519 A 2
6/24/2008 2490197 1,297.00 INS C
6/26/2008 25972 -1,155.00 R
7/2/2008 -1,297.00 V
7/8/2008 1,155.00 A 3
7/2/2008 2495031 1,155.00 INS C
10/9/2008 30148 -1,155.00 R
7/3/2008 2496532 1,155.00 INS C
7/8/2008 -1,155.00 A 3
7/01/08 1234566 50.00 C
2/07/08 12345 -50.00 A
1
7/7/08 -519 C
8/05/08 123456 519.00 A
2
 
Ad

Advertisements

S

Sean Timmons

So you want to group how many A's were in column F on each day, correct?

Easiest to make a little table with your list of dates. Your table would be
in, say, J2:K20 with dates down column J and your count formula below in K

Then do =sumproduct(--($A$2:$A$1000=J2),--($G$2:$G$1000="A"))
 
D

Donna

Sean,
Not sure I explained my problem very well, but you nailed it. Thank you so
much as this will really help me in my reports.
Thanks
Donna
 
Ad

Advertisements

D

Donna

Hi Sean,
When I tried it again either I am doing it wrong or I didn't check it
closely the other day.
What I have for the formula is
=SUMPRODUCT(--($A$2:$A$1000=J2),--($G$2:$G$1000="A"))
After reading your solution again, I think you thought I just wanted to know
how many times each date occured and put that number in each different date
with an A in col F.
Col A COL B COL C COL D COL E COL F COL G COL H COL I COL J COL K
DATES TRANS here's what
it needs to do
4/06/07 A
1
4/07/09 C
4/06/07 A
1
4/25/09 C
4/03/09 A
2
4/09/02 C
4/25/09 C
4/03/09 A
2
In K now I have put what I would want it to be. If column F = "A" then I
would want the dates that match to be numbered in sequence , so on the 1's
above they both have a date of 4/06/07 so anything that had that date would
be 1, then the next one that is "A" is 4/03/09 any of those dates that have
a "A" in column F then all the corresponding dates would be numbered 2, and
so on. Also the adjustments are not in order as mine are above, one A with a
corresponding date may be in several lines throughout the column . I hope I
have explained it better this time. Thanks in advance for looking at this for
me. Donna
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top