Hi all,
I would like to check for duplicates values horizontally
Example: I need to check on 1st June to see if there are more than 1 employee going down to an event on that particular day.
I want to filter out that only 1 person is allowed at 1 event on 1st June.
Is there any formula to use or any way that i can check/trace this?
I try conditional formatting but it also checks for the rest of the month like 2nd June onwards. So i doubt that would work for my scenario.
Thanks in advance.
Hi julian.wee. Welcome to the forum.
Context will help us understand better. Try uploading a small Excel workbook sample.
If you are not familiar with how to do this:
To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
- be sure to desensitize the data
- click “Go Advanced” (next to Post Quick Reply – bottom right),
- scroll down until you see “Manage Attachments”, click that,
- click “Browse”.
- select your file(s)
- click “Upload”
- click “Close window”
- click “Submit Reply”
The file name will appear at the bottom of your reply.
Dave
Hi, Thank you!
I have uploaded it.
For example, On 1st June, how do i flag out Person B and Person A as they are both going to MHC on the same day.
I am not sure I know what you mean by "flag out". This formula in columns M:R returns the names of Persons where more than one are going to the same event on the same day.
Enter in M4. Fill down and across column R.
Formula:
Please Login or Register to view this content.
Hi,
Thanks alot!
Tho the formula seems a little complicated but it worked like a charm!
but could you explain the aggregate(15,6) part? what does the 15,6 represent?
Would like to understand a little more so in future i could apply it to other use!
Edit:
OR
Is there a way to make it show the date that has 2 person going to the same event instead of listing out every person?
which make it only return the value into 1 column instead of 6
Will that make the formula simpler?
Last edited by julian.wee; 05-25-2018 at 04:36 AM.
Hi julian.
My solution would be using a countif formula in Conditional formatting that colors the cells with the same content on the same line.
Hey,
Thanks for your reply! Appreciate it
I tried doing on my own but it highlighted the whole selection instead of horizontally.
EDIT:
Oh. Figured it out already!
Thanks alot guys! appreciate the prompt and useful help!
Cheers
Last edited by julian.wee; 05-25-2018 at 05:48 AM.
yeah, that happend to me too at the first look closely at the $ used or not around the ranges. there is the key in showing results of all lines of just one line
Formula:Please Login or Register to view this content.
make sure only the Column-letters of range have the $-sign the numbers should not have it in front and also the criteria cell needs to be without $
yep i notice it after exploring for abit.
Thanks to both of you! Both of the methods works for me well!
You're welcome. Thanks for the feedback.
AGGREGATE has been called the "Swiss Army Knife" of functions. It can perform many functions ... 19 in fact. 15 is the SMALL function. 6 is an option to ignore errors. AGGREGATE array formulas usually do not have to be array entered ... ie with Ctrl + Shift + Enter. A simple Enter works.could you explain the aggregate(15,6) part? what does the 15,6 represent?
If anyone can help me this problem. I have a sheet with 2000 lines.
Each line represent medications and next to the name of medication is the type of medication represented by number (there are 13 columns of type of medications).
in Each line the numbers (which represent type of medication) repeats itself I would like to get the number which repeats itself next to the specific line.
Administrative Note:
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks