Hello,
So I am trying to generate a rota. I am using the INDEX formula to randomly take people from a list in Column AF and place them into columns B to E
Column I to J represents everyone on leave
My question is how can I stop duplicate values showing up within the same row. I have attached the excel sheet for clarification.
Thanks
If you want no duplicates on the same ROW, please try in B3 and copy downNote: The conditional formatting rules in your sample sheet colours duplicates in Column B, but that is not in the same row, as has been requested. The people in the same row are guaranteed to be different. And columns B to E also do not place people who are (on the same row) in columns I-K.Formula:
Please Login or Register to view this content.
Last edited by HansDouwe; 06-21-2023 at 10:10 PM.
I believe that the column AF represents employee names, and this column must necessarily be a unique and non-repeating list. Therefore, in some instances where there were duplicates, I have replaced them with different names to align with your reality.
Below is solution for Excel 2016 or earlier:
To copy the formula in cell B3 to the right and down:
And, the Conditional Formating formula of cell B3:
Please Login or Register to view this content.
Please Login or Register to view this content.
Quang PT
Administrative Note:
Welcome to the forum.
Is your forum profile showing the version of Excel that you need this to work for? Windows 10 is NOT your Office version.
Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.
The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.
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.
Thanks for the reply. Sorry that was a mistake on my part.
Which version of Excel are you using?
Hi,
Thanks for your help.
It works intially but when I expand it out to include a more complex table , a few of the cells generated NUM error.
As you can see, the formula works well when I when I include columns $P3:$R3 but it generates an error when I included $I3: $AA3. Do you know why that is happening?
Incase, you were not sure what you were seeing. The table is a rota and on the weekends, there is an extra person working.
Please do NOT ignore my question: which version of Excel are you using?
Yor profile says Windows 10 - that is NOT your Excel version. What is your Excel version?
Thanks for the feedback and rep. Glad to have helped.
About your question in Post #7:
Please empty all cells B3:F20 and try in B3 and copy down:Formula:Please Login or Register to view this content.
Sorry, I am not sure what you mean. I am using windows 10 or I am using windows online.
Hello, we are interested in which version of Excel you are using. As you may know, the newer generations of Excel come with a range of new and versatile functions that can be very helpful. Therefore, we would like to know if these new functions can be applied in your file or not.Originally Posted by rg268
Hi,
I am using Excel online and then Windows 10 as well to edit it. Is that ok?
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks