Searching and Matching

Hi all,

I can't figure out how to solve this problem. I'd appreciate it if anyone could help.

As in the attached file, the issue is: for each firm with negative transactions, if all previous positive transactions have the same level, I want the level of these negative transactions to be the same as the previous one.

For example, for firm ABC, the first three transactions are all positive and with level 1. The next transactions (B5 & B6) are negative, so I want the level for these transactions are the same as before (ie, C5 = 1 and C6 = 1).

But for firm DEF, when not all positive transactions have the same level (1, 1, 2), I want the level of next negative transaction (C10) to be "FALSE".

Thanks a lot!

How about

C5: =IF(SUMPRODUCT(--($A$2:$A4=$A5),--($C$2:$C4<>$C4)),FALSE,$C4)
applied to other cells

Based on the assumption that data is sorted by Firm and than negative transactions appear at the end of the listings (ie one does not need to look at transactions below current row when determining consistency of level for given firm)

If C entries are true blanks all formulae can be entered simultaneously by:

1 - highlight Column C
2 - hit F5
3 - select Special -> in resulting dialog select Blanks
4 - enter above Formula and press CTRL + ENTER to confirm.