Hi All,
From an earlier post, AlanK was able to assist in coming up the formula, which works great.
{=IFERROR(INDEX($A$13:$A$29, MATCH(0, COUNTIF($A$2:A2, $A$13:$A$29), 0)),"")}
However, 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.
Could anyone advise a different Approach to eliminate the empty row?
Thank you in advance.
see below:
[TABLE="width: 865"]
<tbody>[TR]
[TD]Invoice number
[/TD]
[TD]Performer
[/TD]
[TD]Descrpition
[/TD]
[TD]Tax rate
[/TD]
[TD]Invoice amount
[/TD]
[TD]Tax Amount
[/TD]
[TD]Currency
[/TD]
[TD]Performance date
[/TD]
[/TR]
[TR]
[TD]PIN100722
[/TD]
[TD]Sweptoff
[/TD]
[TD]PIN200770
[/TD]
[TD]0,00
[/TD]
[TD]18.877,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]03-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100732
[/TD]
[TD]Eastern cleaning
[/TD]
[TD]PIN200773
[/TD]
[TD]23,00
[/TD]
[TD]24.500,00
[/TD]
[TD]4.655,00
[/TD]
[TD]USD
[/TD]
[TD]04-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100737
[/TD]
[TD]DNS
[/TD]
[TD]PIN200788
[/TD]
[TD]0
[/TD]
[TD]10.700,00
[/TD]
[TD]0
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 966"]
<tbody>[TR]
[TD]Invoice number
[/TD]
[TD]Company
[/TD]
[TD]Serial number
[/TD]
[TD]Order number
[/TD]
[TD]Tax rate
[/TD]
[TD]Invoice amount
[/TD]
[TD]Tax Amount
[/TD]
[TD]Currency
[/TD]
[TD]Performance date
[/TD]
[/TR]
[TR]
[TD]PIN100722
[/TD]
[TD]Sweptoff
[/TD]
[TD]PIN200770
[/TD]
[TD]5A1433
[/TD]
[TD]0,00
[/TD]
[TD]18.877,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]03-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100732
[/TD]
[TD]Eastern cleaning
[/TD]
[TD]PIN200773
[/TD]
[TD]5A1885
[/TD]
[TD]23,00
[/TD]
[TD]24.000,00
[/TD]
[TD]4.560,00
[/TD]
[TD]USD
[/TD]
[TD]03-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100732
[/TD]
[TD]Eastern cleaning
[/TD]
[TD]PIN200773
[/TD]
[TD]154DN2
[/TD]
[TD]23,00
[/TD]
[TD]500,00
[/TD]
[TD]95,00
[/TD]
[TD]USD
[/TD]
[TD]03-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100733
[/TD]
[TD]Western cleaning
[/TD]
[TD]PIN200779
[/TD]
[TD]6A8162
[/TD]
[TD]23,00
[/TD]
[TD]650.000,00
[/TD]
[TD]123.500,00
[/TD]
[TD]USD
[/TD]
[TD]03-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100734
[/TD]
[TD]Southern cleaning
[/TD]
[TD]PIN200755
[/TD]
[TD]2A4157
[/TD]
[TD]0,00
[/TD]
[TD]16.950,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]08-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100735
[/TD]
[TD]Northern Cleaning
[/TD]
[TD]PIN200784
[/TD]
[TD]9379M61P03
[/TD]
[TD]23,00
[/TD]
[TD]1.292,50
[/TD]
[TD]245,58
[/TD]
[TD]USD
[/TD]
[TD]08-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100736
[/TD]
[TD]Oriental
[/TD]
[TD]PIN200785
[/TD]
[TD]340-051-901-0
[/TD]
[TD]0,00
[/TD]
[TD]4.740,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100737
[/TD]
[TD]DNS
[/TD]
[TD]PIN200788
[/TD]
[TD]1347M32G08
[/TD]
[TD]0,00
[/TD]
[TD]2.850,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100737
[/TD]
[TD]DNS
[/TD]
[TD]PIN200788
[/TD]
[TD]3A2704
[/TD]
[TD]0,00
[/TD]
[TD]5.000,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100737
[/TD]
[TD]DNS
[/TD]
[TD]PIN200788
[/TD]
[TD]6A7906
[/TD]
[TD]0,00
[/TD]
[TD]2.850,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100738
[/TD]
[TD]KEA
[/TD]
[TD]PIN200789
[/TD]
[TD]340-085-120-0
[/TD]
[TD]0,00
[/TD]
[TD]21.000,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100739
[/TD]
[TD]KLC
[/TD]
[TD]PIN200787
[/TD]
[TD]1523M71G07
[/TD]
[TD]0,00
[/TD]
[TD]2.850,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100739
[/TD]
[TD]KLC
[/TD]
[TD]PIN200787
[/TD]
[TD]1851M59P01
[/TD]
[TD]0,00
[/TD]
[TD]2.850,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100739
[/TD]
[TD]KLC
[/TD]
[TD]PIN200787
[/TD]
[TD]1864M97P01
[/TD]
[TD]0,00
[/TD]
[TD]2.850,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100740
[/TD]
[TD]874EAP
[/TD]
[TD]PIN200792
[/TD]
[TD]1971M17G01
[/TD]
[TD]0,00
[/TD]
[TD]5.000,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]18-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100741
[/TD]
[TD]DLC
[/TD]
[TD]PIN200797
[/TD]
[TD]340-116-401-0
[/TD]
[TD]0,00
[/TD]
[TD]18.000,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]19-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100742
[/TD]
[TD]COPN
[/TD]
[TD]PIN200802
[/TD]
[TD]9511M24P07
[/TD]
[TD]0,00
[/TD]
[TD]7.950,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]22-jan-2018
[/TD]
[/TR]
</tbody>[/TABLE]
From an earlier post, AlanK was able to assist in coming up the formula, which works great.
{=IFERROR(INDEX($A$13:$A$29, MATCH(0, COUNTIF($A$2:A2, $A$13:$A$29), 0)),"")}
However, 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.
Could anyone advise a different Approach to eliminate the empty row?
Thank you in advance.
see below:
- Results will be like this:
<tbody>[TR]
[TD]Invoice number
[/TD]
[TD]Performer
[/TD]
[TD]Descrpition
[/TD]
[TD]Tax rate
[/TD]
[TD]Invoice amount
[/TD]
[TD]Tax Amount
[/TD]
[TD]Currency
[/TD]
[TD]Performance date
[/TD]
[/TR]
[TR]
[TD]PIN100722
[/TD]
[TD]Sweptoff
[/TD]
[TD]PIN200770
[/TD]
[TD]0,00
[/TD]
[TD]18.877,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]03-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100732
[/TD]
[TD]Eastern cleaning
[/TD]
[TD]PIN200773
[/TD]
[TD]23,00
[/TD]
[TD]24.500,00
[/TD]
[TD]4.655,00
[/TD]
[TD]USD
[/TD]
[TD]04-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100737
[/TD]
[TD]DNS
[/TD]
[TD]PIN200788
[/TD]
[TD]0
[/TD]
[TD]10.700,00
[/TD]
[TD]0
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
</tbody>[/TABLE]
Table 1
<tbody>[TR]
[TD]Invoice number
[/TD]
[TD]Company
[/TD]
[TD]Serial number
[/TD]
[TD]Order number
[/TD]
[TD]Tax rate
[/TD]
[TD]Invoice amount
[/TD]
[TD]Tax Amount
[/TD]
[TD]Currency
[/TD]
[TD]Performance date
[/TD]
[/TR]
[TR]
[TD]PIN100722
[/TD]
[TD]Sweptoff
[/TD]
[TD]PIN200770
[/TD]
[TD]5A1433
[/TD]
[TD]0,00
[/TD]
[TD]18.877,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]03-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100732
[/TD]
[TD]Eastern cleaning
[/TD]
[TD]PIN200773
[/TD]
[TD]5A1885
[/TD]
[TD]23,00
[/TD]
[TD]24.000,00
[/TD]
[TD]4.560,00
[/TD]
[TD]USD
[/TD]
[TD]03-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100732
[/TD]
[TD]Eastern cleaning
[/TD]
[TD]PIN200773
[/TD]
[TD]154DN2
[/TD]
[TD]23,00
[/TD]
[TD]500,00
[/TD]
[TD]95,00
[/TD]
[TD]USD
[/TD]
[TD]03-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100733
[/TD]
[TD]Western cleaning
[/TD]
[TD]PIN200779
[/TD]
[TD]6A8162
[/TD]
[TD]23,00
[/TD]
[TD]650.000,00
[/TD]
[TD]123.500,00
[/TD]
[TD]USD
[/TD]
[TD]03-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100734
[/TD]
[TD]Southern cleaning
[/TD]
[TD]PIN200755
[/TD]
[TD]2A4157
[/TD]
[TD]0,00
[/TD]
[TD]16.950,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]08-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100735
[/TD]
[TD]Northern Cleaning
[/TD]
[TD]PIN200784
[/TD]
[TD]9379M61P03
[/TD]
[TD]23,00
[/TD]
[TD]1.292,50
[/TD]
[TD]245,58
[/TD]
[TD]USD
[/TD]
[TD]08-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100736
[/TD]
[TD]Oriental
[/TD]
[TD]PIN200785
[/TD]
[TD]340-051-901-0
[/TD]
[TD]0,00
[/TD]
[TD]4.740,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100737
[/TD]
[TD]DNS
[/TD]
[TD]PIN200788
[/TD]
[TD]1347M32G08
[/TD]
[TD]0,00
[/TD]
[TD]2.850,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100737
[/TD]
[TD]DNS
[/TD]
[TD]PIN200788
[/TD]
[TD]3A2704
[/TD]
[TD]0,00
[/TD]
[TD]5.000,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100737
[/TD]
[TD]DNS
[/TD]
[TD]PIN200788
[/TD]
[TD]6A7906
[/TD]
[TD]0,00
[/TD]
[TD]2.850,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100738
[/TD]
[TD]KEA
[/TD]
[TD]PIN200789
[/TD]
[TD]340-085-120-0
[/TD]
[TD]0,00
[/TD]
[TD]21.000,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100739
[/TD]
[TD]KLC
[/TD]
[TD]PIN200787
[/TD]
[TD]1523M71G07
[/TD]
[TD]0,00
[/TD]
[TD]2.850,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100739
[/TD]
[TD]KLC
[/TD]
[TD]PIN200787
[/TD]
[TD]1851M59P01
[/TD]
[TD]0,00
[/TD]
[TD]2.850,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100739
[/TD]
[TD]KLC
[/TD]
[TD]PIN200787
[/TD]
[TD]1864M97P01
[/TD]
[TD]0,00
[/TD]
[TD]2.850,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]12-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100740
[/TD]
[TD]874EAP
[/TD]
[TD]PIN200792
[/TD]
[TD]1971M17G01
[/TD]
[TD]0,00
[/TD]
[TD]5.000,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]18-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100741
[/TD]
[TD]DLC
[/TD]
[TD]PIN200797
[/TD]
[TD]340-116-401-0
[/TD]
[TD]0,00
[/TD]
[TD]18.000,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]19-jan-2018
[/TD]
[/TR]
[TR]
[TD]PIN100742
[/TD]
[TD]COPN
[/TD]
[TD]PIN200802
[/TD]
[TD]9511M24P07
[/TD]
[TD]0,00
[/TD]
[TD]7.950,00
[/TD]
[TD]0,00
[/TD]
[TD]USD
[/TD]
[TD]22-jan-2018
[/TD]
[/TR]
</tbody>[/TABLE]