How to Consolidate Data in sheet 1 into sheet 2 based on Dates

I could not find any previous thread for the same..

Here i am not combining numbers.. i am trying to combine Text and Number..I want to combine At least the Text ..help appreciated

Remove duplicate all the columns and apply for calculating the amount countifs.

I've added 3 helper columns to my version which can be hidden. Just note that the formulae have only been copied down 100 rows.

It consolidates dates and names + counts occurences + places Yes or No to sent.

Sorry, I just changed one of the three "11/12/2015 Rosie" lines to No and it only changed the count from 3 to 2 and didn't list the unsent line. So I've changed it very slightly to account for an unsent line on the same date as sent ones.

@newdoverman... sorry Pivot is not allowed..

@Beamernsw thanks its working... when i use the same formula in acyual data sheet.. 1st row for agent i am getting 0..and if it is same person it is not summing up and showing

can you help

Does your data start on the same row as your sample? and also are the formulas on the same row?

i.e. row 2....or do your have another heading row and your data starts on row3, or maybe no headings and your data starts on row 1.

That's all I can think of off-hand.

Edit: oh, I get it, that's why you have the numbers beside the date on the data tab....they represent actual row numbers.....brb

i think it works from yesterday s solution i will attach the file.. please see

AS per the new formula.. ifi paste in the above attachment.. i am getting zero in the cell D5...

Can i email you the file i am working if it is ok for you ??

Can you give me ur email ?

@beamernsw

THere was some problem with my browser and its popup blocker then i could not retrieve it back..

Thanks a ton.. it works well .. i will test it thoroughly before implementing..

I am adding reputation to you

Note: Still i yet to interpret your formula and how it works.. i am not expert in excel so

Ok, this will be a bit long

Column A simply joins the date(serial number), Anchor, & Sent into a single text string.

Column B provides a number to sort the exact combination of date,name,sent
B10: =IF(OR(COUNTIF($A$1:A9,A10)>0,A10=""),"",COUNTIF(A:A,"<="&A10))
The first part IF(OR(COUNTIF($A$1:A9,A10)>0,A10=""),"" counts how many times the value in A10 occurs in the cells above (A1:A9)
If any are found (>0) or if A10 is blank then it leaves B10 blank ("")
Now, if it didn't find any occurances above, then the second part kicks in COUNTIF(A:A,"<="&A10)
This then counts of how many cells in column A (A:A) are less than or equal to the value in A10
In the sample I have, B10 is 358. That means there are 358 cells that are either the same as cell A10 or alphabetically less

Column C counts how many times Sent=Yes for the same person on the same date.
I simplified the formula since last time to:
Cell C2: =IF(B2="","",IF(RIGHT(A2,3)="Yes",COUNTIF(A:A,A2),0))
It says that if B2 is blank, leave it blank. Then if B2 ends with "Yes" then count how many times it occurs. If it doesn't end with "Yes" then 0.

Column D grabs the dates from OS!AW
Cell D1: =IFERROR(INDEX(OS!AW:AX,MATCH(SMALL(B:B,ROW(1:1)),B:B,0),1),"")
IFERROR(….,"") simply leaves the cell blank if there are any errors.
Now, working from the inside out….. ROW(1:1) simply gives the answer 1. But as its copied down it increases to 2,3,4,5..etc
SMALL(B:B,ROW(1:1)) or SMALL(B:B,1) finds the 1st smallest number from column B.
SMALL(B:B,ROW(5:5)) or SMALL(B:B,5) finds the 5th smallest number from column B…..etc
On my sample workbook, the 1st smallest number is 350.
So, MATCH(SMALL(B:B,ROW(1:1)),B:B,0) is the same as MATCH(350,B:B,0).
Match is looking down column B to find the first row that has 350 in it. (In my case Row 2).
So now we have, INDEX(OS!AW:AX,2,1)
INDEX is a grid (in this case all of columns AW:AX in the OS sheet) and it grabs the 2nd row of the 1st column (2,1).

Column E is the same except it grabs the 2nd column
E2: =INDEX(OS!AW:AX,2,2) In my case

Column F is the same thing except the grid is column C of the tracking sheet.
F2: =INDEX(C:C,2,1) In my case

Column G simply leaves the cell blank or puts "Yes" or "No" based on Column F
G2: =IF(F2="","",IF(F2>0,"Yes","No"))

Thank you so much for your patience explaining it to me....

Much Appreciated



Thanks a ton to New South Wales from Tamil Nadu South India