+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 21

Data Required in a easy way in a complex file.

  1. #1
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    315

    Data Required in a easy way in a complex file.

    Hi All,
    can anybody help me to set a file as per my desired required DATA. please see the attached file and see the sheet result WANT. dont consider sheet 2 DATA. work only main DATA.
    Thanks.
    Attached Files Attached Files
    Last edited by majidsiddique; 01-11-2019 at 03:10 AM.

  2. #2
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,227

    Re: Set File as per DATA required

    Please try

    A2
    =IFERROR(INDEX('Main-DATA'!B:B,AGGREGATE(15,6,ROW('Main-DATA'!$C$2:$C$9999)/NOT(MMULT(--(LEFT('Main-DATA'!$C$2:$C$9999,4)={"bill",""}),{1;1})),ROWS(A$2:A2)))&"","")

    B2:J2
    =IFERROR(INDEX('Main-DATA'!C:C,AGGREGATE(15,6,ROW('Main-DATA'!C$2:C$9999)/(LEFT('Main-DATA'!$C$2:$C$9999)="m")/ISNUMBER('Main-DATA'!$E$2:$E$9999),ROWS(B$2:B2))),"")

    F2
    =IFERROR(INDEX('Main-DATA'!G:G,AGGREGATE(15,6,ROW('Main-DATA'!G$2:G$9999)/(LEFT('Main-DATA'!$C$2:$C$9999)="m")/ISNUMBER('Main-DATA'!$E$2:$E$9999),ROWS(F$2:F2))-1),"")

    Copy A2:J2 down

    I use custom format # to hide 0 at column D:G
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    315

    Re: Set File as per DATA required

    hi Bo_Ry,
    please see the pic some imp record are incorrect.
    Attached Images Attached Images

  4. #4
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,227

    Re: Set File as per DATA required

    Please provide correct answer and highlight row from Main-DATA that data come from.

  5. #5
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    315

    Re: Set File as per DATA required

    please see the original data sheet against the IMP of main data sheet.
    Attached Images Attached Images

  6. #6
    Registered User
    Join Date
    02-14-2018
    Location
    India
    MS-Off Ver
    2007
    Posts
    13

    Re: Set File as per DATA required

    If you are using Excel 2013 and above you can use "POWER Query"

  7. #7
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    315

    Re: Set File as per DATA required

    Hi, Cak,
    Excel 2010

  8. #8
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,227

    Re: Set File as per DATA required

    Please try again

    A2

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



    B2:J2
    =IFERROR(INDEX('Main-DATA'!C:C,AGGREGATE(15,6,ROW('Main-DATA'!C$2:C$9999)/MMULT(--(LEFT('Main-DATA'!$C$2:$C$9999)={"m","s","a"}),{1;1;1})/ISNUMBER('Main-DATA'!$E$2:$E$9999),ROWS(B$2:B2))),"")


    F2
    =IFERROR(INDEX('Main-DATA'!G:G,AGGREGATE(15,6,ROW('Main-DATA'!G$2:G$9999)/MMULT(--(LEFT('Main-DATA'!$C$2:$C$9999)={"m","s","a"}),{1;1;1})/ISNUMBER('Main-DATA'!$E$2:$E$9999),ROWS(F$2:F2))-1),"")
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    315

    Re: Set File as per DATA required

    Hi Bo_Ry,
    Excellent work, only one IMP No miss, but forget.
    your work is very smart. i spent too much time to set it.
    Thanks.
    can you explain this formula? if you dont mind.
    Thanks.

  10. #10
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,227

    Re: Set File as per DATA required

    The main part is column C

    MMULT(--(LEFT('Main-DATA'!$C$2:$C$9999)={"m","s","a"}),{1;1;1})

    (LEFT('Main-DATA'!$C$2:$C$9999)="m")+(LEFT('Main-DATA'!$C$2:$C$9999)="s")+(LEFT('Main-DATA'!$C$2:$C$9999)="a")

    We only want rows that column C that start with "m" or "s" or "a"
    Above 2 formula will give result 1 if start with "m" or "s" or "a" and 0 if not

    Row()/MMULT() gives row number that start with "m" or "s" or "a" and if not give #DIV/0

    AGGREGATE(15,6,Row()/MMULT(),k) give a small number that not include error #DIV/0, we use this row with Index to get result from column B:J

    Except for column F that need to offset -1 row, so -1 adds after aggregate.


    Column A is quite complicated, too hard to explain.


    After review Isnumber is not needed, we can remove Isnumber from all formula.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    315

    Re: Set File as per DATA required

    hi Bo_Ry,
    Thanks for explanation, big formula is to difficult for understand. i apply it on formula sheet. can you tell me where i am missing to find out IMP No. i got item but miss last one. please tell me where i am missing?
    Attached Files Attached Files

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    46,927

    Re: Set File as per DATA required

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )

    Also, Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    315

    Re: Set File as per DATA required

    Hi FDibbins,
    i know very well about this. this is not a training center but in this topic i want some extra knowledge regarding the formula. actually our routine life we have to face so many different challenges to work with different situation. so that, i request to Bo_Ry to explain this because i have to apply this formula in other situation. if i put a new thread for this, i think this is not a suitable.
    Thanks for your rules guidance.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    46,927

    Re: Set File as per DATA required

    I am not asking you to start a new thread, I am asking, firstly, to change your thread title to something that actually describes your request, and secondly, to explain HERE in the thread, what that problem is, and what you expect.

    Dont make members open your file, just to see what you are asking

  15. #15
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    315

    Re: Set File as per DATA required

    Ok, i understand.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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