A formula to remove duplicate vales from one column while keeping all other information.

[hitower74]

We have an accounting program that doesn't have the best reporting functions, so I need some help to get rid of some info.

We have the invoice number in column B which is displayed for every item that was invoice for that sale. The problem is it also does this to the freight charge in column F. We are trying to find out how much freight we charged for a certain period but this is obviously giving us false readings, ie, an order with 5 items and a total freight cost of $55 will give us $275.

So I am wondering if there is a formula that says something like 'If information in Column B is a duplicate then delete information on corresponding row in column F.

You can also tell me I'm a pillock and this isn't possible.

I've attached the spreadsheet. Some cells have been manually deleted but there are just too many with not much time to do it that way.

Cheers,

[FDibbins]

Nope, no pillock, thios is relatively simple

This wont delete those duplicate values (formulas cant do that), but put this in G5 and copy down...
=IF(COUNTIF($B$5:B5,B5)>1,"",F5)

Now, you can either just work from that column, and leave things as they are, or, you can copy/paste values over the original data, then delete those formulas.

On a side note, this formula will pull out all unique Inv Numbers for you. However, because of the volume, it will slow your file down. So, if you want to use it, copy/paste values once you have finished.

=IFERROR(INDEX($B$5:$B$2405,MATCH(0,INDEX(COUNTIF($J$4:J4,$B$5:$B$2405),),0)),"")
copied down

You can then use that list to sum/extract your invoice values

[hitower74]

Absolutely perfect! Thanks for your help!