# formula to extract multiple rows based on reference

1. ## 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?

2. ## 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. ## Re: formula to extract multiple rows based on reference

Hereby I attach the workbook.

Hopefully it is clear.

4. ## Re: formula to extract multiple rows based on reference

=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

5. ## 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?

6. ## Re: formula to extract multiple rows based on reference

The expected result is in sheet 2

Thank you.

7. ## 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

8. ## 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. ## Re: formula to extract multiple rows based on reference

That's pity.
This is very useful tool

Have a nice day

10. ## 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. ## Re: formula to extract multiple rows based on reference

Hey John,

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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

20. ## 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. ## 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. ## Re: formula to extract multiple rows based on reference

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

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

#### 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