+ Reply to Thread
Results 1 to 6 of 6

How to extract Data from a large list via Date (One Day Summary)

  1. #1
    Registered User
    Join Date
    12-01-2018
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    2

    Question How to extract Data from a large list via Date (One Day Summary)

    Hi Everyone!
    I'm new in the forum. I stuck with a problem which I explain here in attached image. Excel Problem.jpg
    Basically I want to extract data DATE wise from a large list with different entries which contain Slip No & Amount. I just want to change Date and the respective data show in below section as shown in picture.
    Condition!! For the Fee & Funds Section I reserve only 15 rows downward, but I want to paste same Data regarding fee & funds but no 16th row. I want that 16th row in next column 1st row and continues downward for next 15 entries. So I don't know how to order a formula which continues with some rows and same as in next column. (Hope this make sense). Thanks in advance for helping.
    Last edited by Umerbilal7860; 12-01-2018 at 06:15 AM.

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,717

    Re: How to extract Data from a large list via Date (One Day Summary)

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    12-01-2018
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    2

    Re: How to extract Data from a large list via Date (One Day Summary)

    Hi alansidman, Thanks for your response to my post. I understand what you said. Now I attached herewith an excel file with mockup sheet and one with complete blank but Layout created for adding functions in the file. Please make it live I'll be very thank full to you for this. Waiting for your response again.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,050

    Re: How to extract Data from a large list via Date (One Day Summary)

    Please try at

    I6 copy to L6 and copy down

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    M6:N6 copy down

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($D$4:$D$999)/($D$4:$D$999=$M$4)/($F$4:$F$999=$J$2),ROWS(M$6:M6))),"")

    O6 copy down

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($D$4:$D$999)/($D$4:$D$999=LEFT($O$4,5))/($F$4:$F$999=$J$2),ROWS(O$6:O6)))&"","")

    P6 copy down

    =IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($D$4:$D$999)/($D$4:$D$999=LEFT($O$4,5))/($F$4:$F$999=$J$2),ROWS(P$6:P6))),"")

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: How to extract Data from a large list via Date (One Day Summary)

    I came up with the same as Bo_Ry for the first two parts except reference numbers in column H for the SMALL k_values.

    I interpreted the 3rd part differently. I removed the trailing "s" from the 'Others' header and then this in O6:P20.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,274

    Re: How to extract Data from a large list via Date (One Day Summary)

    Pl see file and note changes made.
    NOTE:O4 changed to Other by removing s.
    O5 changed as Remarks

    In I6 then copied across to L20
    Please Login or Register  to view this content.
    In M6 then copied across to P20
    Please Login or Register  to view this content.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-25-2015, 06:15 AM
  2. Replies: 2
    Last Post: 08-17-2015, 10:53 PM
  3. best way to filter and extract data from large data list
    By boltonlad2k in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2014, 11:26 AM
  4. Replies: 4
    Last Post: 01-18-2013, 05:10 AM
  5. [SOLVED] Extract Data From Rec to Summary
    By Fili Rojas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-21-2012, 02:09 PM
  6. Extract data into summary sheet
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 11-17-2012, 11:17 AM
  7. Logic- Extract Data into summary form from a list
    By Harlequin in forum Excel General
    Replies: 2
    Last Post: 02-01-2007, 07:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1