value from Column A when IF is "yes" in Column B

[slamanager]

Hi,

Ive looked for several days now to get this formula working. I have a list in col D (text), in col A i look for a unique item in col D in col B i look for another unique value in col D these items dont match on the same row.

And here's the "but" when i do find my items in col B how do I combine them with my col A item then i can pivot the items to total them.

Normally i get one item in col A to multiple items in col B they are randomly spaced apart in the list, then i get a new item in col A so on and so on. I should have 11 items in A but may have less or repeats.

Ive looked at the formulas that "find value above cell" etc - my list starts at row 11 and only goes to row 5000

i used =LOOKUP(2,1/(A$1:A1<>""),A:A) from thread in MRExcel Re: Get first non-blank cell above a certain cell? posted by Rick Rothstein

 

[Oeldere]

show a small example of your data on the forum.

 

[slamanager]

Col A formula looks for the word "Server" in Col D and returns the 6 digits after the word Server.
Col B formula looks for the word "Added / Removed / Updated" and returns the value "A R or U".
Col C would be the combination of the 6 digit server id and the A R U value shown as 123456A or 123456R or 123456U
Col D is the list supplied starting cell D11:D????

The formulas in Col A and B will return "" if they cannot locate their info

Col A, Col B, Col C, Col D
"","","",Start date 2015/10/12 08:00
123456,"","",Server 123456
"","","",aaaa bbbb cccc
"","","",qqqq rrrr ssss
"",A,123456A,xxx yyyy zzzz added xxxx yyyy zzzz
"","","",xxxx yyyy zzzz
"","","",zzzz yyyy xxxx
"",R,123456R,xxx yyyy zzzz Removed xxxx yyyy zzzz
"","","",aaaa bbbb cccc
"","","",dddd eeee ffff

 

[slamanager]

show a small example of your data on the forum.

Col A formula looks for the word "Server" in Col D and returns the 6 digits after the word Server.
Col B formula looks for the word "Added / Removed / Updated" and returns the value "A R or U".
Col C would be the combination of the 6 digit server id and the A R U value shown as 123456A or 123456R or 123456U
Col D is the list supplied starting cell D11:D????

The formulas in Col A and B will return "" if they cannot locate their info

Col A, Col B, Col C, Col D
"","","",Start date 2015/10/12 08:00
123456,"","",Server 123456
"","","",aaaa bbbb cccc
"","","",qqqq rrrr ssss
"",A,123456A,xxx yyyy zzzz added xxxx yyyy zzzz
"","","",xxxx yyyy zzzz
"","","",zzzz yyyy xxxx
"",R,123456R,xxx yyyy zzzz Removed xxxx yyyy zzzz
"","","",aaaa bbbb cccc
"","","",dddd eeee ffff

 

[Oeldere]

The green cells are the result.

The yellow cells contains a formula (helpcolumn).

Book1
	A	B	C	D	E	F
9	Col A	Col B	Col C	Col D
10
11				Start date 2015/10/12 08:00
12	123456		123456	Server 123456
13	123456		123456	aaaa bbbb cccc
14	123456		123456	qqqq rrrr ssss
15	123456	A	123456A	xxx yyyy zzzz added xxxx yyyy zzzz	A
16	123456		123456	xxxx yyyy zzzz
17	123456		123456	zzzz yyyy xxxx
18	123456	R	123456R	xxx yyyy zzzz Removed xxxx yyyy zzzz		R
19	123456		123456	aaaa bbbb cccc
20	123456		123456	dddd eeee ffff
Blad1

Cell Formulas
Range	Formula
A11	=IF($A10<>"",$A10,IFERROR(IF(SEARCH("server",$D11,1)>=1,RIGHT($D11,6),""),""))
B11	=IF($E11<>"",$E11,IF($F11<>"",$F11,""))
C11	=A11&B11
E11	=IFERROR(IF(SEARCH("added",$D11,1)>1,"A"),"")
F11	=IFERROR(IF(SEARCH("Removed",$D11,1)>1,"R"),"")