Formula to extract data into a list based on value in a specific column

[demonicsoul]

Hi,

I'm looking for a VBA formula where if in the value in column A = 1, then E1 = B1 and E2 = C1. Then E3 = B2 and so on down the list. Screenshot of date and result below. Thanks

 

[Ezguy4u]

demon Let's get the ball rolling. Now this usually generates more questions than answers, but we have to start somewhere. Something is going on here and I can't explain it but E3 has the formula of =IF($A$1=1,B2,"") and E4 has the formula of =IF($A$1=1,B3,"") so keep that in mind. Question comments put them below.

Book1
	A	B	C	D	E
1	1	cat	dog		cat
2	2	cow			dog
3	1	cat	dog		cow
4	3	horse			cat
5					dog
6					horse
Data

Cell Formulas
Range	Formula
E1	E1	=IF($A$1=1,B1,"")
E2	E2	=IF($A$1=1,C1,"")
E3:E4	E3	=IF($A$1=1,B2,"")
E5	E5	=IF($A$1=1,C3,"")
E6	E6	=IF($A$1=1,B4,"")

 

[Peter_SSs]

@demonicsoul

I suggest that you update your Account details (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’)

I know that you have asked for vba, but if you have the TOCOL function (hence my suggestion above about version info) then it becomes very simple by formula.

23 08 15.xlsm
	A	B	C	D	E
1	1	cat	dog		cat
2	2	cow			dog
3	1	cat	dog		cow
4	3	horse			cat
5					dog
6					horse
7
TOCOL

Cell Formulas
Range	Formula
E1:E6	E1	=TOCOL(B1:C4,1)
Dynamic array formulas.

 

[Ezguy4u]

Well you can never have too many solutions so here are my entries. These programs do seem a little odd but maybe they will spark some more conversation.

Sub Prog2()

If Range("A1") = 1 Then

Range("E1").Formula = "=B1"
Range("E2").Formula = "=C1"
Range("E3").Formula = "=B2"
Range("E4").Formula = "=B3"
Range("E5").Formula = "=C3"
Range("E6").Formula = "=B4"

End If
End Sub

If Range("A1") = 1 Then

Range("E1") = Range("B1")
Range("E2") = Range("C1")
Range("E3") = Range("B2")
Range("E4") = Range("B3")
Range("E5") = Range("C3")
Range("E6") = Range("B4")

End If

End Sub

 

[demonicsoul]

Thanks for the suggestions. sorry for the confusion, but basically, my logic is:

When there is a "1" in column A, apply values in columns B and C into 2 rows.
When there is a "2" in column A, only apply the value in column B
and so on down the rows

not sure if that clarifies it more, but i'll see if your suggestions will work. thanks again

 

[Peter_SSs]

When there is a "1" in column A, apply values in columns B and C into 2 rows.
When there is a "2" in column A, only apply the value in column B

.. but in your example in post #1, row 4 has a 3 in column A & you have not explained what to do in that circumstance or whether even more different numbers might appear in column A.

 

[demonicsoul]

yes you're right. the 3 should have been a 2. there would only be two variables (1 or 2). 1 = data in two columns; 2 = data in only one column. thanks

 

[Peter_SSs]

OK, so post #3 formula gives the expected results for the sample data in post #1 as far as I can see. Is that correct?

If so, can you give some other small sample data and the expected results (with XL2BB so we can copy it for testing) where the post #3 formula does not give the expected results?