+ Reply to Thread
Results 1 to 21 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
    2013-365
    Posts
    500

    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
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    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
    2013-365
    Posts
    500

    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
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    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
    2013-365
    Posts
    500

    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
    2013-365
    Posts
    500

    Re: Set File as per DATA required

    Hi, Cak,
    Excel 2010

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

    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
    2013-365
    Posts
    500

    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
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    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
    2013-365
    Posts
    500

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    2013-365
    Posts
    500

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    2013-365
    Posts
    500

    Re: Set File as per DATA required

    Ok, i understand.

  16. #16
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: DATA required in a easy way

    please see my new TITLE.

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Set File as per DATA required

    Without seeing what you want to do, I cannot say if that title is more descriptive or not, sorry.

  18. #18
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: Set File as per DATA required

    i changed new tiltle in title thread, but not showing?
    Title:
    Data Required in a easy way in a complex file.

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Set File as per DATA required

    Yes, I can see you changed the title (you need to do it in the 1st post though)

    Sorry to be such a pain, but I am still waiting to see you describe what your question is, and what you expect?

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

    Re: Set File as per DATA required

    Please see the correction in blue.

    G2
    =IFERROR(IF(COUNTIF(G$1:G1,LOOKUP("z",A$2:INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$102)/MMULT(--(LEFT($B$2:$B$102)={"m","s","r","y"}),{1;1;1;1})/ISNUMBER($D$2:$D$102),ROWS(G2:G$2))))),"",LOOKUP("z",A$2:INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$102)/MMULT(--(LEFT($B$2:$B$102)={"m","s","r","y"}),{1;1;1;1})/ISNUMBER($D$2:$D$102),ROWS(G2:G$2))))),"")

    H2
    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$102)/MMULT(--(LEFT($B$2:$B$102)={"m","s","R","y"}),{1;1;1;1})/ISNUMBER($D$2:$D$102),ROWS(B2:B$2))),"")
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    Re: Data Required in a easy way in a complex file.

    Thanks a lot Bo_Ry.

+ 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. Macro Required for attaching a file through Browse option in excel file
    By vikash200418 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-18-2015, 12:38 AM
  2. A solution is required for a Data Cleansing File
    By ladyv in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-23-2013, 08:50 PM
  3. Help required in matching data in required format
    By pravindwd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-04-2013, 09:03 PM
  4. Help required in matching data in required format
    By pravindwd in forum Access Tables & Databases
    Replies: 1
    Last Post: 05-04-2013, 04:11 PM
  5. Required different location data to different file
    By Premmadaan in forum Excel General
    Replies: 3
    Last Post: 04-06-2012, 05:25 AM
  6. macro required to filter data on several tabs and save to a file, then repeating action
    By Stevengreen22 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-04-2012, 10:26 AM
  7. Get data from protected file, changes required in existing code
    By aravindhan_31 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2009, 06:42 AM

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