AND/OR/IF Statement - Return list with no repetitions

[tlc53]

Hi,

I am doing my head in with this formula and was wondering if someone could help me please??

[TABLE="width: 500"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]Rent for Store - April 2015[/TD]
[TD]123/001[/TD]
[/TR]
[TR]
[TD]Rent for Apartment - April 2015[/TD]
[TD]123/001[/TD]
[/TR]
[TR]
[TD]Office Room Hire - April 2015[/TD]
[TD]123/001[/TD]
[/TR]
[TR]
[TD]Client payment on account[/TD]
[TD]123/001[/TD]
[/TR]
[TR]
[TD]Something else - bananas[/TD]
[TD]123/001[/TD]
[/TR]
[TR]
[TD]Something else - oranges[/TD]
[TD]123/003[/TD]
[/TR]
[TR]
[TD]Something else - grapes[/TD]
[TD]123/001[/TD]
[/TR]
[TR]
[TD]Something else - pears[/TD]
[TD]123/003[/TD]
[/TR]
[TR]
[TD]Something else - apples[/TD]
[TD]123/001[/TD]
[/TR]
[TR]
[TD]Something else - kiwifruit[/TD]
[TD]123/008[/TD]
[/TR]
</tbody>[/TABLE]

I have a table similar to the above with 10 rows. I then have a table with 10 rows below. If 10 different codes were entered, all 10 rows would be populated. However, in example above, I would want it to return the following;

123/001
123/003
123/008
"Blank"
"Blank"
"Blank"
"Blank"
"Blank"
"Blank"
"Blank"

So it does not repeat any of the codes.

The first formula is straight forward as it just looks at the first cell and returns the value if populated. The second formula on gets a little trickier. My current formula returns 123/003123/008 which is 123/003 and 123/008. I want it to only return 123/003.

=IF(B3=B2,"",B3)&IF(OR(B4=B3,OR(B4=B2)),"",B4)&IF(OR(B5=B4,OR(B5=B3,OR(B5=B2))),"",B5)&IF(OR(B6=B5,OR(B6
=B4,OR(B6=B3,OR(B6=B2)))),"",B6)&IF(OR(B7=B6,OR(B7=B5,OR(B7=B4,OR(B7=B3,OR(B7=B2))))),"",B7)&IF(OR(B8=
B7,OR(B8=B6,OR(B8=B5,OR(B8=B4,OR(B8=B3,OR(B8=B2)))))),"",B8)&IF(OR(B9=B8,OR(B9=B7,OR(B9=B6,OR(B9=B5,
OR(B9=B4,OR(B9=B3,OR(B9=B2))))))),"",B9)&IF(OR(B10=B9,OR(B10=B8,OR(B10=B7,OR(B10=B6,OR(B10=B5,OR(B10
=B4,OR(B10=B3,OR(B10=B2)))))))),"",B10)&IF(OR(B11=B10,OR(B11=B9,OR(B11=B8,OR(B11=B7,OR(B11=B6,OR(B11=
B5,OR(B11=B4,OR(B11=B3,OR(B11=B2))))))))),"",B11)

Once I have this working, I was then going to get adjacent cells to sum up the values depending on the code.

Thank you for your time!

 

[Peter_SSs]

Try this, copied down.

Excel Workbook
	A	B
1	Description	Code
2	Rent for Store - April 2015	123/001
3	Rent for Apartment - April 2015	123/001
4	Office Room Hire - April 2015	123/001
5	Client payment on account	123/001
6	Something else - bananas	123/001
7	Something else - oranges	123/003
8	Something else - grapes	123/001
9	Something else - pears	123/003
10	Something else - apples	123/001
11	Something else - kiwifruit	123/008
12
13
14		Code
15		123/001
16		123/003
17		123/008
18
19
Unique List

.. I was then going to get adjacent cells to sum up the values depending on the code.

There are no values to sum in your post. Where are they? In column C?

Have you considered using a Pivot table that will both determine the unique values and sum the amounts for you?

 

[tlc53]

Thank you so much for that! It works perfectly and was just what I was after.
Your answer was so nicely presented, it made it very easy to follow.
Thanks again. Much appreciated!!!

 

[Peter_SSs]

Thank you so much for that! It works perfectly and was just what I was after.
Your answer was so nicely presented, it made it very easy to follow.
Thanks again. Much appreciated!!!

You are very welcome. Thanks for your kind words.