+ Reply to Thread
Results 1 to 22 of 22

formula to extract multiple rows based on reference

  1. #1
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    formula to extract multiple rows based on reference

    Hi All,

    I have the following formula which works great except for 1 issue.

    If i would Change the A2 to A1, it would give me Errors, since the extract of this is the succession of certain pervious entries, I don't want to split an empty row in between, otherwise it makes it impossible to implement the filter function.


    Invoice number Company Serial number Order number Tax rate Invoice amount Tax Amount Currency Performance date
    PIN100722 Sweptoff PIN200770 5A1433 0,00 18.877,00 0,00 USD 03-jan-2018
    PIN100732 Eastern cleaning PIN200773 5A1885 23,00 24.000,00 4.560,00 USD 03-jan-2018
    PIN100732 Eastern cleaning PIN200773 154DN2 23,00 500,00 95,00 USD 03-jan-2018
    PIN100733 Western cleaning PIN200779 6A8162 23,00 650.000,00 123.500,00 USD 03-jan-2018
    PIN100734 Southern cleaning PIN200755 2A4157 0,00 16.950,00 0,00 USD 08-jan-2018
    PIN100735 Northern Cleaning PIN200784 9379M61P03 23,00 1.292,50 245,58 USD 08-jan-2018
    PIN100736 Oriental PIN200785 340-051-901-0 0,00 4.740,00 0,00 USD 12-jan-2018
    PIN100737 DNS PIN200788 1347M32G08 0,00 2.850,00 0,00 USD 12-jan-2018
    PIN100737 DNS PIN200788 3A2704 0,00 5.000,00 0,00 USD 12-jan-2018
    PIN100737 DNS PIN200788 6A7906 0,00 2.850,00 0,00 USD 12-jan-2018
    PIN100738 KEA PIN200789 340-085-120-0 0,00 21.000,00 0,00 USD 12-jan-2018
    PIN100739 KLC PIN200787 1523M71G07 0,00 2.850,00 0,00 USD 12-jan-2018
    PIN100739 KLC PIN200787 1851M59P01 0,00 2.850,00 0,00 USD 12-jan-2018
    PIN100739 KLC PIN200787 1864M97P01 0,00 2.850,00 0,00 USD 12-jan-2018
    PIN100740 874EAP PIN200792 1971M17G01 0,00 5.000,00 0,00 USD 18-jan-2018
    PIN100741 DLC PIN200797 340-116-401-0 0,00 18.000,00 0,00 USD 19-jan-2018
    PIN100742 COPN PIN200802 9511M24P07 0,00 7.950,00 0,00 USD 22-jan-2018


    Results will be like this:


    Invoice number Performer Descrpition Tax rate Invoice amount Tax Amount Currency Performance date
    PIN100722 Sweptoff PIN200770 0,00 18.877,00 0,00 USD 03-jan-2018
    PIN100732 Eastern cleaning PIN200773 23,00 24.500,00 4.655,00 USD 04-jan-2018
    PIN100737 DNS PIN200788 0 10.700,00 0 USD 12-jan-2018

    Here is my formula in A3

    #{=IFERROR(INDEX($A$13:$A$29, MATCH(0, COUNTIF($A$2:A2, $A$13:$A$29), 0)),"")}

    Could anyone advise a different Approach to eliminate the empty row?

    Thank you in advance.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: formula to extract multiple rows based on reference

    You should by now that posting data in the thread is not very helpful so please :


    Attach a sample workbook (not image).so that we do not have to manually key in your data to do a testing.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: formula to extract multiple rows based on reference

    Hereby I attach the workbook.

    Hopefully it is clear.
    Attached Files Attached Files

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: formula to extract multiple rows based on reference

    Instead your formula:
    =IFERROR(INDEX($A$3:$A$19,MATCH(0,COUNTIF($A$2:A2,$A$3:$A$19), 0)),"")

    shange the highlited area with this and array entered

    =IFERROR(INDEX($A$3:$A$19,MATCH(0,COUNTIF($A$22:A22,$A$3:$A$19), 0)),"")

    For another columns, put this on B23 and copied down and cross:
    =IFERROR(VLOOKUP($A23,$A$3:$I$19,COLUMNS($A$1:B1),FALSE),"")

    and hope this works
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: formula to extract multiple rows based on reference

    Thank you for your Response azumi.

    It works great. However I want to have the extries extracted to a different sheet, without the 1 gap row,

    because you extract the data pasted in row 23, and in the formula referring to row 22

    is there any possibility to alter the formula to eliminate it?

    Thank you in advance !

  6. #6
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: formula to extract multiple rows based on reference

    The expected result is in sheet 2

    Thank you.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: formula to extract multiple rows based on reference

    if you are able to use PowerQuery you can try this one (result on sheet3)

    Power Query for
    Last edited by sandy666; 06-07-2018 at 11:25 AM. Reason: typo

  8. #8
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: formula to extract multiple rows based on reference

    Hey Sandy,

    Thank you for your quick Response:-)

    However I am using Excel 2010, without the power query function. and not able to insert any Add-in

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: formula to extract multiple rows based on reference

    That's pity.
    This is very useful tool

    Have a nice day

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: formula to extract multiple rows based on reference

    COUNTIF requires the use of a "Header" row which you show in your original post:

    Invoice number Performer Descrpition Tax rate Invoice amount Tax Amount Currency Performance date
    PIN100722 Sweptoff PIN200770 0,00 18.877,00 0,00 USD 03-jan-2018
    PIN100732 Eastern cleaning PIN200773 23,00 24.500,00 4.655,00 USD 04-jan-2018
    PIN100737 DNS PIN200788 0 10.700,00 0 USD 12-jan-2018
    so what is the issue now? You cannot use COUNTIF without this row.

  11. #11
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: formula to extract multiple rows based on reference

    Hey John,

    Thanks for your reply.
    The reason I don’t want to include the header row is due to the fact that I will already manually type in thousands of rows in the past, and I would like to implement the formula next to the most recent row. It won’t be able to filter the data I need if I have a gap row in between

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: formula to extract multiple rows based on reference

    I don't follow what you mean: it not obvious to my very old brain (looking at your posted file) when you talk about a gap.

    to implement the formula next to the most recent row.

  13. #13
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: formula to extract multiple rows based on reference

    I am sorry I didn't mean to confuse you.

    Perhaps it is more apparent if you see the sample workbook.

    If I use the formula in row 34, that row will be empty. therefore, row 35 onward are not in the filter range.

    Perhaps it is more clear now.

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: formula to extract multiple rows based on reference

    @John
    Maybe use any Energizer? or for old brain the best is coffee and brandy

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: formula to extract multiple rows based on reference

    I am still confused: you have "new" data in rows 23 to 33 yet formula refers to data in A3:A19.

    I am obviously suffering from a (more frequent) "senior moment" but I would filter to a new sheet (as per sheet2)

    I'll leave it to others !!!!

  16. #16
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: formula to extract multiple rows based on reference

    So basically row 3 to 19 is the output data from another sheet.

    row 23 to 33 is old data that are manually typed in.

    from row 35 is the data using the formula.

    However I would like to have the data in row 35 instead of 34.

    Thank you.

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: formula to extract multiple rows based on reference

    O.K: I think you will need VBA to do this based on the way your data is input/organised.

    Or extract to separate sheet and then copy/paste to the "master" sheet.

  18. #18
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: formula to extract multiple rows based on reference

    I don't think VBA is applicable for me.

    Thank you anyway for your help..!

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: formula to extract multiple rows based on reference

    Is this statement (post #16) mistyped "However I would like to have the data in row 35 instead of 34" ?
    I thought that the objective was to get rid of the empty row (34).
    Using the file attached to post #13, I selected cell A35 and dragged the fill handle back up to A34. The values all moved up a row so that all rows between 22 and 45 were filled.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  20. #20
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: formula to extract multiple rows based on reference

    Hey JeteMc,

    Thanks for your post, actually it is not a mistype. since I would like to have the result in row 35 to be listed in row 34.
    Would that be possible?
    Thank you.

  21. #21
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: formula to extract multiple rows based on reference

    Did you try selecting cell A35 and dragging the fill handle back up to cell A34?
    Let us know if you have any questions.

  22. #22
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: formula to extract multiple rows based on reference

    I'm not sure but I'm revised my previous formula to other sheet
    Attached Files Attached Files

+ 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. [SOLVED] Extract data to multiple rows based on value in separate cell
    By jehster1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2018, 02:41 AM
  2. Index Problem to extract multiple rows based in one column
    By chrisk67 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-16-2016, 07:42 AM
  3. Replies: 1
    Last Post: 04-06-2016, 07:02 AM
  4. Replies: 1
    Last Post: 03-17-2013, 06:52 AM
  5. extract rows from sheet based on multiple criteria
    By frederikk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2012, 04:12 AM
  6. Replies: 1
    Last Post: 08-21-2012, 02:46 PM
  7. Extract multiple rows from multiple worksheets with Index formula
    By RedApple in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2011, 02:33 PM

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