Text populate containing more records based on date

[abraham30]

Hello Everyone!

I asked my question in few forum but did not get proper result. can anybody help me.

I want to display text name with highest and second highest record in columns B and C, their total records in columns D and E.

Formula should work based on the date of sheet 'template', match with the Download_DT column of sheet 'Total'

[Aussiebear]

Your post is a little ambiguous to say the least. I'm guessing here but are the "records" you speak of, simply the highest and 2nd highest count per day by each individual?

[abraham30]

Thanks Aussie for spending your valuable time in looking into the matter.

My qustion is that is it possible to populate the check which contain highest number of records in column B of "Template" based on specific date.

e.g. Suppose I want to see the check with date "01/12/2013" from Download_DT column of sheet 'Total'.

The date contain a total of 18 records for 6 checks out of which

ACC_001
6


ACC_002
5


ACC_003
1


ACC_004
1


ACC_007
4


SAE_001
1







As ACC_001 contain a maximum of 6 records,it will be populated in column B2 and the total records will be displayed in column D2.

[SamT]

Highlighted example of one date. I sorted the orginal for illustrative purposes.

[abraham30]

Thanks SamT .. I haven't found any formula or macro in the sheet.

It is possible to find highest record by manual sorting but there is lakhs of records, we can not spend time to find check with highest record.

i don't know whether its possible or not as I have not that much knowledge on Excel.

Thanks

[Aussiebear]

I have partially found a solution, which may lead to one of the wiser members to suggest another method.

Firstly I created a table using lists of Unique Dates(row) and unique Check values(Column), then used Sumproduct to find the counts of Checks per Date,
Then I used the Large function to determine which were the top two positions per Date,
Then I used the IndexMatch function to determine which Check held the 1st & 2nd Values, but

It fails (only allocates the first instance) if the counts are duplicated.

[SamT]

Abraham,

What if: thre checks are the same for top place?



ACC_001
5


ACC_002
5


ACC_003
5




Also, what if two checks have the same value for second place?



ACC_001
6


ACC_002
5


ACC_003
5