EXCEL FORMULA HELP

Find a developer for your Excel triggered betting needs and advertise your development service here.

Moderator: 2020vision

EXCEL FORMULA HELP

Postby BERTRAND » Mon Sep 05, 2011 10:54 am

Hi
I have two columns of values (integers), A and B. The values in column B are unique and refer to corresponding values in A which are duplicated many times for each unique value. I need to replace these duplicates in A with blanks in order to facilitate their corresponding ROW deletion.
Last four days spent trawling through Excel help sites to no avail, so any help greatly appreciated.
Bert
BERTRAND
 
Posts: 99
Joined: Thu Feb 03, 2011 4:15 pm

Postby osknows » Mon Sep 05, 2011 11:15 am

Hi Bert,

Insert this formula EXACTLY into a spare column, row 1 and copy down. For instance in cell [Z1] write

=COUNTIF($A$1:A1,A1)

This will increment the count of unique items in column A. Eg if '0001' occurs 4 times then the first occurrence will be numbered 1, second 2, third 3 etc.

You can then filter the column with this equation and delete anything > 1 which will be a duplicate
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby BERTRAND » Mon Sep 05, 2011 12:56 pm

Hi Os
Column A consists entirely of blocks of duplicates, I only want to delete the blocks that correspond to the unique values in column B.
Maybe I'm missing something but you didn't mention column B!
Bert
BERTRAND
 
Posts: 99
Joined: Thu Feb 03, 2011 4:15 pm

Postby osknows » Mon Sep 05, 2011 1:14 pm

I thought column B was just a complete unique list of column A.

So it's a subset of column A and you only want to delete duplicates in A if it appears in column B?

When you say "Column A consists entirely of blocks of duplicates" - do you mean that you could have an integer repeat say 10 times together as a block of 10 and then appear 100's of rows later as another block of 10?

Using the example above, when deleting duplicates do you want 1 row only with no duplicates or 1 block with 10 duplicates?

It may be easier to post an example :)
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby BERTRAND » Mon Sep 05, 2011 1:51 pm

So it's a subset of column A and you only want to delete duplicates in A if it appears in column B? TRUE

When you say "Column A consists entirely of blocks of duplicates" - do you mean that you could have an integer repeat say 10 times together as a block of 10 TRUE

and then appear 100's of rows later as another block of 10? FALSE

Using the example above, when deleting duplicates do you want 1 row only with no duplicates or 1 block with 10 duplicates? I want 1 block with 10 duplicates.
BERTRAND
 
Posts: 99
Joined: Thu Feb 03, 2011 4:15 pm

Postby osknows » Mon Sep 05, 2011 3:42 pm

Sorry, you'll have to PM an example as I'm not quite sure what it is your trying to do
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby osknows » Mon Sep 05, 2011 6:08 pm

Thanks for the info. In any column use this formula and copy down to indicate rows to delete

=IF(COUNTIF(B:B,A1),"DELETE ROW","")
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby BERTRAND » Mon Sep 05, 2011 7:51 pm

Brilliant!
Thanks Os
I must really take a closer look at the COUNTIF function
BERTRAND
 
Posts: 99
Joined: Thu Feb 03, 2011 4:15 pm


Return to Find an Excel developer

Who is online

Users browsing this forum: No registered users and 8 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.