Remove Duplicates in Column & Move All Other Values Into Same Row

[funkdoctaspock]

Good Evening, I have a export file for work that will export employee shifts by job code. In column A of the file there will be ~ 500 rows. I think what I am trying to achieve can be best explained as: I will have repeating values in column A (employee name like Dave, Tom etc etc.) It is only necessary to keep the first instance of the employee's name in column A. In columns B-H (days of the week) employees will have different jobs assigned (not included on the export / not important for this exercise). Complicating things would be that Dave could have 4 different job codes each of which would show in a different row. I will need to group all values in columns B:H into the first row (with the employee's name) so that they may read a proper work schedule when posted. Currently I cannot post a dummy file for an example - but does anyone have any good suggestions on how to accomplish this? I have searched quite a bit and nothing is jumping out at me. What I can paste into this post looks like this - MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY DAVE 8-5 7-3 DAVE 8-5 8-5 8-5 DAVE 7-3 7-3 DAVE I would like it to read Dave 8-5 7-3 (I think you get the picture) Any help would be greatly appreciated - VBA is ok too. Thanks for reading!

 

[István Hirsch]

A copyable sheet of the original layout along with the desired result (entered manually) would help replying.

 

[funkdoctaspock]

A copyable sheet of the original layout along with the desired result (entered manually) would help replying.

Thank you Istvan -

I see below my post here that I "may not post attachments"

I can upload to a sharepoint and see if that is within the forum rules. If it is not - please see table below. This what one employee would look like after the file gets exported. The end file could have any number of employees so the solution would have to be "dynamic" for sure.

ID	Name	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
989	Matt			11-5
989	Matt	8-3			9-3
989	Matt		7-4			4-10
989	Matt

<tbody>
</tbody>

And achieve

ID	NAME	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
989	Matt	8-3	7-4	11-5	9-3	4-10

<tbody>
</tbody>

Thanks for reading!

 

[István Hirsch]

If you can list the employees names (or do you want this to be done by the formula too), you can do someting like this. Copy the formula in cell C13 and confirm with Ctrl + Shift + Enter. Then drag the formula across and down :

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	ID	Name	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	989	Matt			11-May
3	989	Matt	08-Mar
4	989	Matt		07-Apr			04-Oct
5	989	Matt
6	990	Pat	09-Mar		22-Feb		11-Jun
7	990	Pat		04-Jan		06-Mar
8	989	Matt				09-Mar
9	990	Pat
10
11
12	ID	Name	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
13	989	Matt	08-Mar	07-Apr	11-May	09-Mar	04-Oct
14	990	Pat	09-Mar	04-Jan	22-Feb	06-Mar	11-Jun
Sheet1

 

[funkdoctaspock]

If you can list the employees names (or do you want this to be done by the formula too), you can do someting like this. Copy the formula in cell C13 and confirm with Ctrl + Shift + Enter. Then drag the formula across and down :

Sheet1

	A	B	C	D	E	F	G	H	I
1	ID	Name	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	989	Matt			11-May
3	989	Matt	08-Mar
4	989	Matt		07-Apr			04-Oct
5	989	Matt
6	990	Pat	09-Mar		22-Feb		11-Jun
7	990	Pat		04-Jan		06-Mar
8	989	Matt				09-Mar
9	990	Pat
10
11
12	ID	Name	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
13	989	Matt	08-Mar	07-Apr	11-May	09-Mar	04-Oct
14	990	Pat	09-Mar	04-Jan	22-Feb	06-Mar	11-Jun

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C13 {=INDEX(C$2:C$9,MATCH(1,($B$2:$B$9=$B13)*(C$2:C$9<>""),0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Works great! Thank you Istvan.

May I ask a follow up question -

How would this work with a named dynamic range(s)? For example -in the workbook I'll name one range "SHIFTS" & another one "EMP_NAME"

SHIFTS = OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$B:$B)-1,7) where C2 would be the first shift below Monday in the header row.

EMP_NAMES=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!C:C)-1,1)

I'd like to revise the formula to read something like this

=IFERROR(INDEX(shifts,MATCH(1,(emp_names=$B2)*(shifts<>""),0)),"")

(confirmed with Ctrl + Shift + Enter)

This would seem to cover the ever changing export from the facility's scheduling application safely and eliminate the need for absolute references. I've tried it quickly in my workbook and it doesn't seem to work - mostly NA errors.

Thank you for your help so far, you've been great!

 

[István Hirsch]

Your are welcome, thanks for the feedback.

I do not know why your version with named ranges does not work. An analysis using the Formula/Evaluate function may help to locate the function where the N/A error comes from.