Looking for a formula to help me highlight duplicates. I have a spreadsheet with column A having a bunch of different names and column E have an 8 digit number in it. I need this spreadsheet to highlight all the duplicate numbers in column E if column A is that same person. Something like if column A equal column A and if column E equal column E then highlight column E
Last edited by AliGW; 04-14-2017 at 04:21 AM.
Will this work for you?
Add a column and concatenate the name and number into one string
Example added column is F
Formula in F2 (copied down) is
Formula:
Please Login or Register to view this content.
CF formula (based on selecting whole of column E to format) is
Formula:Please Login or Register to view this content.
CF_concat.jpg
If this will not give you what you want then please attach:
a small sample Excel workbook -not a picture
- max 50 rows of normal data plus expected results (in separate sheet perhaps)
- good sample data => best solution
- remove confidential information
To attach your file:
- click Reply / Go Advanced / scroll down to Manage Attachments / follow instructions (top of screen)
Last edited by kev_; 04-14-2017 at 03:25 AM.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
Yes that helps but some of the cells in column E are blank. Is there a way I can exclude the blank ones?
Why would they be blank? The reason may affect any suggestion made.
PS You really do need to give more thought to your thread titles in future - this one is far too generic to be of any real use. Thanks!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Amend the CF formula to
Formula:Please Login or Register to view this content.
As AliGW intimates, "blank" does not always mean empty - so the above solution would not work if (for example) the cell in column E contained a supressed zero
Perhaps a better generic CF formula (for your normal rule of 8 digit numbers)
Formula:Please Login or Register to view this content.
Last edited by kev_; 04-14-2017 at 04:00 AM.
Okay I'm sorry I'm new to this. So this is the report that I get at work. It tracks every transaction we do and puts it into a spreadsheet. When we sign on to our computers and sign of it logs it. Those transactions don't have anything in column E. I need to see if some people are doing duplicate transactions throughout the day and I figured highlighting them would help me out.
So could you please manually highlight a few examples in your file, including some where that column is blank?
Attached is your workbook with concatenation in column I (had to reduce file size to attach - so deleted last 2000 rows)
Same method as above, with references to column F changed to column I
eg rows 383 and 386 highlighted
Last edited by kev_; 04-14-2017 at 05:06 AM.
Thanks Kev_ that worked. Is there a way I can take it farther now and copy the whole row where column A is different but column E is the same and put that information in K through S like I have in my example?
I know you are new to the forum, but this is a completely different request! Please try to state everything that you want from the outset to avoid wasting people's time. Are there any other 'tweaks' that you are going to be asking for?
this is it. I just had this idea as I was working on this
OK - in that case, as it is a completely different request, please start a new thread, and make sure that the thread title reflects what you are now asking for. You should also mark this thread as solved. Thanks!
Please explain your condition better.
- where column A is different to what
- column E is the same as what
Also to make it totally clear, please attach your file (the first 2000 rows are sufficient)
and this time:
- mark up the first 20 rows that should be copied with an "Y" in column K
- mark up the first 5 rows that should NOT be copied with a "N" in column K
thanks
It may be a few hours before I reply - other tasks to attend to....
I will look out for your new thread as per AliGW request
Do not refer to this thread - make the new thread totally standalone - nobody should need to come here to pick up any pieces.
Last edited by kev_; 04-14-2017 at 05:29 AM.
Please mark this thread as solved now that you have opened a new thread. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks