Get unique values from 3 different columns and put them into 1 column

[jehster1]

Hi all! I've searched the forums and google and can't come up with what I believe has to be an easy answer on my own. It's possible I'm not searching for the right terms.

I need to get the names from columns A, B and C then put the results into Column D. I would prefer it to do this on the fly via formula as the names will change as people will call out and get replaced for their 'shifts'. In a perfect world it would also not bring over any duplicate names already in column D (though I could highlight/delete dupes manually later if needed). It might be important to note that in the real sheet these columns are NOT right next to each other.

Attached are the before and after sheets.

I'm also fine with using helper columns on a separate sheet if needed.

Thank you!

[MarvinP]

Hi jehster,

You could do a copy and paste to the bottom to get all the names to column D. The real question is how do you want this to work? Is VBA allowed? Double click on a name and it appears next in column D? Will you be doing this in front of others? Will the names change in each shift to other shifts or be deleted completely? There are a lot of unknowns here.

No formula pops into my mind to get all those names to column D. I do see Conditional Formatting that might show duplicates that might help pick out who hasn't be moved yet.

Can you explain your process more?

[jehster1]

Hi Marvin. Thank you for the quick reply! I'm trying to stay away from Copy and Paste as I'm looking for something more automatic. VBA could work, I'm just not very familiar with it other than finding code on the internet and tweaking it to do what I want.

I've attached the actual sheets I made and filled them with sample data.

The bigger picture is this is a sheet where I check people in and I enter in hours worked and it gets sent to payroll.

I'll try to break down the relevant process as best I can. These are for rock and roll shows so the 3 shifts are Load In, Show Call and Load Out. I'll get the Load In callsheet (50-200 people) from the office maybe the day before and I'll import it to the Master Call Sheet (MCS). Between the time I get the callsheet and when Load In actually starts a couple people will already have called out and get replaced. I'll make the change on the MCS. The MCS info gets pushed to the Check In (CI) sheet and I'll check people in/out from that.

At about noon I'll choose people from the Load In to work the Show Call (maybe 5-10%). I enter those manually via drop-down on the MCS. That gets pushed to CI sheet and I check them in/out from there.

The office sends me the Load Out list which usually consists of mostly the same people from Load In plus or minus others. That list get imported in MCS and the info pushed to CI sheet.

SO, by the end of the night I have 3 shifts of various people who have worked all or part of each shift with various pay rates depending on their position during each shift.

I would like to put those peoples names into 1 big list that I so I can total their hours and pay and push that info into my timesheet for payroll. I've got it all mostly figured out except how to get the names from the 3 different columns into 1 big list.

I hope that's not too confusing. Thank you.

[CAntosh]

I don't know if it's the most elegant solution, but I think I have something that works. Using the post#3 attachment, try entering the formula below in W6. The formula must be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

=IFERROR(IFERROR(IFERROR(INDEX(Table18[Load In - Name],SMALL(IF(COUNTIF($W$4:$W5,Table18[Load In - Name])=0,ROW(Table18[Load In - Name])-ROW(Table18[[#Headers],[Load In - Name]])),1)),INDEX(Table19[ShowCall - Name],SMALL(IF(COUNTIF($W$4:$W5,Table19[ShowCall - Name])=0,ROW(Table19[ShowCall - Name])-ROW(Table19[[#Headers],[ShowCall - Name]])),1))),INDEX(Table21[LO - Name],SMALL(IF(COUNTIF($W$4:$W5,Table21[LO - Name])=0,ROW(Table21[LO - Name])-ROW(Table21[[#Headers],[LO - Name]])),1))),"")

It basically pulls the names that haven't been listed already from the LI group, then it moves to the SC group, then finally the LO group. It shouldn't list duplicates. Fill the formula down as far as you need to. It's an array formula, which will calculate more slowly than normal formulas, so I wouldn't advise filling it down dramatically further than you need to, but give it enough space and it should automatically update as the 3 individual tables are altered/updated.

Caveat: I know it seems odd, but make sure that W4 stays blank. Otherwise one blank row will show up in your list of names. There are other ways around the issue if you positively must use W4, but this is the simplest fix.

Take a look at the attachment to see if it's working as desired:

[jehster1]

Hey, Thanks! I'm about to run out for a bit but will try it when I get back home and let you know how it goes.

[jehster1]

It looks like it worked! I even changed some of the references b/c I added some columns to the table to account for some things I hadn't thought of before. I'll play around with it more tomorrow and see if I can break it. I don't know how you came up with that but wow, that's a doozy of a formula lol. Thanks a lot CAntosh and MarvinP (I would like to see your VBA solution as it may be something I could use later!)

[CAntosh]

I'm glad I could help! The formula looks scarier than it is - it's actually the same formula nested inside itself 3 times to account for the 3 different columns it might pull from. Try using 'Evaluate Formula' on the Formulas tab to step through it piece by piece to get a feel for what it's doing.

Good luck!

[FlameRetired]

jehster1 while you are at it you might have a look at tim's solution to a similar post (post #2) here. Perhaps combining some of CAntosh's approach with tim's formula will do what you want.