Go Back   Forums @ The Digital Fix > Gadgets and Gizmos > Computing Forum

Reply
 
Thread Tools Display Modes
Old 07-02-2020, 20:57   #1
AdamBrunt
Trusted User
 
Join Date: Sep 2000
Location: UK
Posts: 24,843
Thanks: 136
Thanked 285 Times in 199 Posts
Calling all Excel gurus .... a data list question

Hi all,

I have been tasked with coming up with a spreadsheet for a client to keep track of some sales data.

On one of the sheets they want to have 3 columns which a sales person fills in from a pre-defined list of values - all simple enough so far. However, the underlying values are the same across all three columns BUT the same value can not be chosen in more than column; ideally with the lists in the other two columns changing as the value in any of the columns changes. The sales person also needs to be able to enter data in multiple rows.

So ... they want to provide a list of values, say, 'A,B,C,D' and they want a 3 column by unlimited row sheet where each cell contains one item from the list (or is empty) and a value in the list can only appear at most once per row but as many times as they want per column.

Any one got ideas if this possible ? And is it likely to be possible using just the List GUI ? Or likely to need some VBA type script/macro ?
AdamBrunt is offline   Reply With Quote
Old 08-02-2020, 13:14   #2
ColinD
________________________
 
Join Date: Jul 2000
Location: Middlewich, Cheshire
Posts: 1,022
Thanks: 258
Thanked 85 Times in 39 Posts
Sounds like your might need to do down the VBA route for that.

Try here as well:
https://old.reddit.com/r/excel/

if none of the forums boffins can help.
__________________
If you really wanted to screw me up. You should have got to me earlier.
ColinD is offline   Reply With Quote
Old 08-02-2020, 13:42   #3
doogie
PSN doogstar
 
Join Date: Mar 2001
Location: XBL doogsta69
Posts: 1,062
Thanks: 26
Thanked 48 Times in 41 Posts
Your quick and dirty is just smacking them in the face with a validation hint, either on the end, or using conditional formatting. OR (A1=B1, A1=C1, B1=C1) will cover that

Doing something like dynamically changing the available options is a bit of a head scratcher. I've looked at ways to do it using OFFSET and SUBSTITUTE with Array Formula Cells to remove the already selected value, but even with TRIM it's currently including a blank option, which is messy, and it also takes up a load of overflowing cells to build the list, which is not likely to work well either.

Essentially you want to do an array subtraction - your starting set is [A,B,C,D] and when you make one selection [B] in A1 you want the options to be [A,B,C,D] - [B] = [A,C,D] and then when you select an option in B1 [C] you want the options to be [A,B,C,D] - [B,C] = [A,D] but excel doesn't really like doing that.

Might have a play about with it more later.

Last edited by doogie; 08-02-2020 at 13:45.
doogie is offline   Reply With Quote
Old 08-02-2020, 15:47   #4
scoobyood
Tony Danza
 
scoobyood's Avatar
 
Join Date: Aug 2004
Location: Zagreb
Posts: 8,757
Thanks: 823
Thanked 1,100 Times in 548 Posts
Have many options are in each list? If it's not too crazy and they aren't going to change, you concat your three columns into a field and then create a list which hold all combinations which the concat field 'isn't allowed to be'. Then it's a lookup against the 'violations' list.
scoobyood is offline   Reply With Quote
Old 10-02-2020, 11:19   #5
AdamBrunt
Trusted User
 
Join Date: Sep 2000
Location: UK
Posts: 24,843
Thanks: 136
Thanked 285 Times in 199 Posts
Thanks for all the help people.

Been looking at this more, with the help of someone over @ mrexcel.com, over the weekend [ I will post the link to the thread later ] and it seems it is relatively easy to do ( and scalable ) as long as you use Excel 365.

Now I just need to work out if we use 365 here

https://www.mrexcel.com/board/thread...a-row.1123429/

Last edited by AdamBrunt; 10-02-2020 at 12:51.
AdamBrunt is offline   Reply With Quote
Old 11-02-2020, 22:58   #6
doogie
PSN doogstar
 
Join Date: Mar 2001
Location: XBL doogsta69
Posts: 1,062
Thanks: 26
Thanked 48 Times in 41 Posts
Ah interesting solution and informative - It's been a while since I hardcore Excel geek'd out, and so I wasn't aware of the new function FILTER which allows you to easily do the remove this set from this other set function.
doogie is offline   Reply With Quote
Old 12-02-2020, 17:05   #7
AdamBrunt
Trusted User
 
Join Date: Sep 2000
Location: UK
Posts: 24,843
Thanks: 136
Thanked 285 Times in 199 Posts
Quote:
Originally Posted by doogie View Post
Ah interesting solution and informative - It's been a while since I hardcore Excel geek'd out, and so I wasn't aware of the new function FILTER which allows you to easily do the remove this set from this other set function.
Good isn't it provided you are using Office 365 of course
AdamBrunt is offline   Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Another Excel Question -- Calling Chris Locke! : ) Ratfink Computing Forum 6 09-05-2016 12:15
[WEB] Calling all SEO gurus .... AdamBrunt Computing Forum 6 03-02-2012 12:42
Calling all Sun gurus' / Sun StorageTek C2 Guest 62302 Computing Forum 0 06-12-2007 14:30
Calling all you network gurus ! Guest 20 Computing Forum 13 10-10-2005 20:00
Calling all XML gurus .... AdamBrunt Computing Forum 1 22-09-2004 17:29

All times are GMT. The time now is 04:14.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
Copyright ©2000 - 2018 Poisonous Monkey Ltd. Part of The Digital Fix Network