Microsoft Excel - Match data sequence based on Date sheet Column A ,B

Asked By farrukh on 25-Jun-12 04:29 AM
Hello Expert !

I have a 3 sheets in a work book  the main one is "Date" i need to short other sheets data based on columnA dates and column B Name like sheet "Data".  The other sheets always in columnA is date and columnB are names?

Currently i unable to  attached the files?

Pichart Y. replied to farrukh on 25-Jun-12 11:08 AM
Hi hammeed,

Sorry I don't understand your requirement..... :(

Try to attach the file...this morning I could not, but now I can attach the file   :)

pichart Y.

farrukh replied to Pichart Y. on 25-Jun-12 01:01 PM

Finally attached
Pichart Y  the file is attached now i need my main sheet is name of "Date"   and i need to sort the data of other sheet based on column A and B . In column A dates and in B names are i need that code give the sequence to data to all the other sheets based on "Date" Sheet . If possible that all the date of other sheets come to "Date" Sheet after last colum of "Date" Worksheet.

Thanks and Regards,

Sample data
farrukh replied to Pichart Y. on 25-Jun-12 10:36 PM
Dear Pichart,

I am clarify more hope i understand well . I am attaching a file in worksheet (Date) you can see the after processed data which i need sort the data based on dates and names of Date worksheet.

Thanks and Regards,

Pichart Y. replied to farrukh on 26-Jun-12 02:54 AM
Hi Hameed,

Oh I see, I understand your point now...
  • This, you can use sumifs( ) 
  • here is the sample formula =SUMIFS(YeartoDate!C:C,YeartoDate!$A:$A,$AA15,YeartoDate!$B:$B,$AB15)
    • in excel 2003, we can sumif( ) with only 1 criteria, but start from excel 2007, we can use sumifs( ) to sum with many criteria. And your question here is suit with sumifs( )
  • Normally when I work something like this, I will not have repeated column A and B info, I will have only 1 set of them on the left, as I shown in the table below...any way depends on your work though!
  • Sample file for you.
Hope this help.

Pichart Y.
farrukh replied to Pichart Y. on 26-Jun-12 03:10 AM
Dear Pichart Y,

Yes your  new design, just propose is very fine we not need to repeat the columns :) . One thing i need to ask are you matching data of other sheets to sample before it was "Date" Sheet (match case with column A contain Date and column B contain names)?

Thanks and Regards
Pichart Y. replied to farrukh on 26-Jun-12 04:21 AM

Some explain...

From this formula =SUMIFS(YeartoDate!C:C,YeartoDate!$A:$A,$AA15,YeartoDate!$B:$B,$AB15)

  • This is the formula to match the value from sheet YeartoDate, the formula says...
    • sum the value in column C of sheet "YeartoDate"....if.....(2 Conditions follow)
    • if the column A of sheet YeartoDate = the value in AA15 (active sheet, no matter what it's name is)
    • and if the column B of sheet YeartoDate= the value in AB15 (Active sheet....same)
  • this different to what you will see when you use the mouse to click at the cell AA15 to formulate, you will get the sheet name too, if you say your sheet name is Date, then it will be Date!AA15
    • =SUMIFS(YeartoDate!C:C,YeartoDate!$A:$A,Date!$AA15,YeartoDate!$B:$B,Date!$AB15) 
    • but in fact you can remove the Date!, if you want to do.
Hope this help.

Pichart Y.
farrukh replied to Pichart Y. on 26-Jun-12 10:16 AM
Dear Pichart Y,

Thank you for explain i am really sorry i am not getting it can you please do me a favour iam attaching my original. First sheet is the main sheet in column A,B  same dates and names . Can you please add the formula in the white area according to sheets ? I have to apply this to 2000 Rows to be match with green highlighted columns AB of first sheet iam really apologize...

Thanks and Regards,

Revised sample for
Pichart Y. replied to farrukh on 26-Jun-12 11:26 AM
Hi Hameed,

Never mind, here It is, with formula...

pichart Y.
farrukh replied to Pichart Y. on 02-Jul-12 05:28 AM
Dear Pichart Y.

Thank you so much for provide such a great help and support best of wishes and regards.

Thank you