Conditional Match and Post

[InNeedOf]

Tried my best on the title..
Here is what I need to do.
Have two sheets in a excel workbook.
Sheet names:
PNET RAW DATA
SHEET3

In Sheet 3 I want to look up a value in a column from PNET RAW DATA and for every iteration where the cell value is POSSIBLE CONFLICT, pull the value of another cell in PNET RAW DATA and show that value in the cell in SHEET3 or an array in SHEET3 or even a pivot table?
If it DOES NOT match Possible Conflict I want it to ignore until it finds a match.
End result a LIST of ONLY those which have possible conflict in array named Presto or column AI in PNET RAW DATA.

Been working with this this far.. And this pulls it properly but does not continue
=IF('Pnet Raw Data'!AI:AI="Possible Conflict",'Pnet Raw Data'!$A:$A,"--")
--
Thanks!

 

[Akashwani]

Hi,

Welcome to MrExcel.

Is this something like you want to do?...

Excel Workbook
	A	B
1	Name	Status
2	tom	Ok
3	****	Ok
4	Harry	Possible Conflict
5	Sally	Ok
6	Mary	Ok
7	Jim	Possible Conflict
8	Jill	Possible
9	Jo	Conflict
10	Joe	Ok
11	Tim	Ok
12	Stan	Possible Conflict
13	tom	Ok
14	Dom	Ok
15	MrExcel	Possible Conflict
16	Sally	Possible
17	Mary	Conflict
18	Jim	Ok
19	Homer	Ok
20	John	Possible Conflict
21	Joe	Ok
22	Tommy	Ok
23	Ak	Possible Conflict
24	Tony	Possible
25	Dan	Conflict
PNET RAW DATA

Excel Workbook
	A	B	C	D
1	count	Status	*	Names
2	6	Possible Conflict	*	Harry
3	*	*	*	Jim
4	*	*	*	Stan
5	*	*	*	MrExcel
6	*	*	*	John
7	*	*	*	Ak
Sheet3

The formula in D2 needs entering with ctrl shift enter NOT enter.
Copy the formula down as far as you require.

You will need to change the cell ranges to suit your situation.

I hope that helps.

Good luck.

Ak

 

[T. Valko]

Hi,

Welcome to MrExcel.

Is this something like you want to do?...

Excel Workbook
	A	B
1	Name	Status
2	tom	Ok
3	****	Ok
4	Harry	Possible Conflict
5	Sally	Ok
6	Mary	Ok
7	Jim	Possible Conflict
8	Jill	Possible
9	Jo	Conflict
10	Joe	Ok
11	Tim	Ok
12	Stan	Possible Conflict
13	tom	Ok
14	Dom	Ok
15	MrExcel	Possible Conflict
16	Sally	Possible
17	Mary	Conflict
18	Jim	Ok
19	Homer	Ok
20	John	Possible Conflict
21	Joe	Ok
22	Tommy	Ok
23	Ak	Possible Conflict
24	Tony	Possible
25	Dan	Conflict
PNET RAW DATA

Excel Workbook
	A	B	C	D
1	count	Status	*	Names
2	6	Possible Conflict	*	Harry
3	*	*	*	Jim
4	*	*	*	Stan
5	*	*	*	MrExcel
6	*	*	*	John
7	*	*	*	Ak
Sheet3

The formula in D2 needs entering with ctrl shift enter NOT enter.
Copy the formula down as far as you require.

You will need to change the cell ranges to suit your situation.

I hope that helps.

Good luck.

Ak

If you index the entire column then you don't have to calculate an "offset correction".

Array entered**

=IF(ROWS(D$2:D2)>$A$2,"",INDEX('PNET RAW DATA'!$A:$A,SMALL(IF('PNET RAW DATA'!$B$2:$B$25=$B$2,ROW('PNET RAW DATA'!$A$2:$A$25)),ROWS(D$2:D2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.