Fill one tab with certain non contiguous cells from another tab

[ozbrian]

Sorry for the poor description.

I have a list of items on the first sheet of a workbook.
Only items in column A that have a value in column B (it can be any value), need to be copied to the second sheet.

If I use something like =if(sheet1!B1="","", A1) and fill this down, I get the data but also all the blanks which then requires a copy/paste and sort to bring them together.

Is there a formula I can use to copy just the relevant items to the second tab and miss out on the blanks?



FIRST TAB DATA
A B
__
1 a
2
3 c
4
5 a
6 j
7
8


SECOND TAB CURRENT
A B
__
1

3

5
6



SECOND TAB - REQUIRED
A B
__
1
3
5
6

[avk]

Instead of snap shot, attach sample file with before & after result required.

[ozbrian]

Instead of snap shot, attach sample file with before & after result required.

I've tried to attach a sample file.
Thanks

[avk]

In second tab sheet "B2"

Please Login or Register  to view this content.

In Second Tab Required sheet "B2"

Please Login or Register  to view this content.

[ozbrian]

Thanks avk!

A couple of issues.
Firstly, the formula is returning the data from column B instead of column A.
Also, is there any way of doing away with the "Second Tab Required" worksheet altogether. I only had it there to show the output I was wanting on the second tab.

[JeteMc]

Try the following:
1) Select cell A2 on the Second Tab sheet,
2) Paste the following array entered formula* into the formula bar:

Please Login or Register  to view this content.

3) Simultaneously press the *Ctrl, Shift and Enter keys
4) copy down as far as needed
Let us know if you have any questions.