Copy Values and skip blank cells? - Vlookup

[Andrewgray123]

Hello,

Just a bit of background first, i'm doing a very simple IF Statement/Vlookup;

My RAW data as an example is formatted like so;

Item (Col A)	Quantity (Col B)
A	10
B
C	50

I've then got another sheet which is the same as above; however i'm going to do an if statement, so it reads IF(B2 > 0) Then Vlookup to return the values in B2, ELSE "")
So the sheet with the Vlookup would then look like;

Item (Col A)	Quantity (Col B)
A	10
C	50

I want to display this information, without the missing row. Obviously i could format it as a table, then hide the rows, but is there a cleaner way to hide these rows (which have returned a null value from a VLookup)

The reason behind this, the RAW data sheet could possibly contain 250 lines; the target sheet may only have 30 values populated out of these 250 lines.

Could anyone offer any hints or tips on how to achieve this?

Many Thanks

Andy

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Andrewgray123]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks,

Done that there and popped it in;

i'm using Microsoft® Excel® for Microsoft 365 MSO (16.0.14131.20278) 32-bit on Windows 10.

Many Thanks

Andy

 

[Fluff]

Thanks for that how about something like

=FILTER(Sheet1!A2:B100,Sheet1!B2:B100>0)

 

[Andrewgray123]

Thanks for that how about something like

=FILTER(Sheet1!A2:B100,Sheet1!B2:B100>0)

Thanks, i've used excel for years and never once used the FILTER command!

Never worked with > 0 however it worked perfectly with <> ""; think i may have to adapt it slightly in the future to return <> "" OR > 0

Many Thanks

Andy

 

[Fluff]

Do you have formulae in in col B of your data sheet?