Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching dates to cells
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching dates to cells
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")) "Donna" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching dates to cells
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 "Sean Timmons" wrote: 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")) "Donna" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching dates to cells
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 "Donna" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
matching data to dates - please help | Excel Worksheet Functions | |||
Matching dates | Excel Discussion (Misc queries) | |||
Help with Matching Text Fields - Then Moving the Matching Cells Side by Side | Excel Discussion (Misc queries) | |||
macro paste by matching dates | Excel Discussion (Misc queries) | |||
Matching 2 "dates" that are formated differently | Excel Worksheet Functions |