Transfer data from one worksheet to another while skipping blanks

[hatmix5]

Hi,

In Sheet 1 I have the below data:

A	1
B	2
B	3
A	4

Then in Sheet 2 I have another worksheet with these formulas in Cells A1-A4 independently:
Cell A1 =IF('Sheet 1'!$A1="A",'Sheet 1'!B1," ")
Cell A2 =IF('Sheet 1'!$A2="A",'Sheet 1'!B2," ")
Cell A3 =IF('Sheet 1'!$A3="A",'Sheet 1'!B3," ")
Cell A4 =IF('Sheet 1'!$A4="A",'Sheet 1'!B4," ")

This produces the following table on Sheet 2:

1
4

How can I make it so the two blanks in between the 1 and 4 are skipped, while also allowing the numbers/blanks to move depending on how the data in Sheet 1 is changed (i.e. the order of A's and B's). The table would look like this:

1
4

Thank you

 

[Zot]

Not really sure what you were looking for but this was what I found

Book1
	A	B
1	A	A
2	B	A
3	B
4	A
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=IFERROR(INDEX(A$1:A$4,SMALL(IF(A$1:A$4="A",ROW(A$1:A$4)-ROW(A$1)+1),ROWS(B$1:B1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
Another option

+Fluff 1.xlsm
	A	B
1	A	1
2	B	2
3	B	3
4	A	4
5
Sheet1

+Fluff 1.xlsm
	A
1	1
2	4
3
4
Sheet2

Cell Formulas
Range	Formula
A1:A4	A1	=IFERROR(INDEX(Sheet1!$B$1:$B$100,AGGREGATE(15,6,(ROW(Sheet1!$A$1:$A$100)-ROW(Sheet1!$A$1)+1)/(Sheet1!$A$1:$A$100="A"),ROWS(A$1:A1))),"")