Excel - align column values

Question

Hello,

I have a sheet that contains time data for hundreds of employees and thousands of rows. I want to compare the dates in columns B and D. If they don't match I want the value (a number) in column E to move down until B=D. The formula should stop when it encounters a blank row and/or when the name in column A changes but restart for the next employee

	Date	Hrs Worked	Date	Meal Break
Grout, Baby
	01/13/2022	8.53	01/13/2022	0.57
	01/14/2022	8.22	01/14/2022	0.7
	01/17/2022	8.22	01/17/2022	0.68
	01/18/2022	8.2	01/18/2022	0.7
	01/19/2022	2.95	01/20/2022	0.55
	01/20/2022	8.55	01/24/2022	0.62
	01/21/2022	3.03	01/25/2022	0.8
	01/24/2022	8.46	01/26/2022	0.68
	01/25/2022	8.8	01/27/2022	0.72
	01/26/2022	8.37	01/28/2022	0.63
	01/27/2022	9.25	08/09/2021	1
	01/28/2022	8.84	08/10/2021	0.5
	08/09/2021	8	08/11/2021	0.55
	08/10/2021	8.15	08/12/2021	0.5
Lord, Star
	11/08/2021	8.07	11/11/2021	0.67
	11/09/2021	8.55	11/12/2021	0.65
	11/10/2021	8.15	11/15/2021	0.8
	11/11/2021	8.09	11/16/2021	0.77
	11/12/2021	8.43	11/17/2021	0.85
	11/15/2021	8.47	11/18/2021	0.85

Answer 1

Have you tried it? Feel free to share any update.

Answer 2

Hi,

It will help if you share 2 worksheets in a workbook. One which has the source data and another which has the result.

Answer 3

1. Select A3:A23,>Ctrl+G>Select Blanks

Input formula in formula bar: = A2

>Ctrl+enter

2. Formula in F3

=SUMIFS($E$3:$E$23,$A$3:$A$23,A3,$D$3:$D$23,B3)

Answer 4

Hello,

Thanks for the response. There is an IF statement in my helper column that alerts me when the dates in columns B and D don't match.

This employee didn't take a meal break on 6/4/2021. I need to shift the date at D4 down to D5 then delete the word SHIFT from E4. I'll repeat the process for each day on which no break was taken (6/, 6/10, etc.)

There are over 45k rows in my document. I'm hoping for a solution that is less time consuming.

B	C	D	E	F
Date	Hrs Worked	Date	Helper Column	Meal Break
05/28/2021	9.04	05/28/2021		0.52
06/01/2021	8.65	06/01/2021		0.53
06/02/2021	10.39	06/02/2021		0.52
06/04/2021	8.68	06/05/2021	SHIFT
06/05/2021	6.65	06/07/2021	SHIFT	1.15
06/06/2021	4.95	06/08/2021	SHIFT
06/07/2021	9.26	06/09/2021	SHIFT	0.52
06/08/2021	8.2	06/11/2021	SHIFT	1.23
06/09/2021	4.03	06/14/2021	SHIFT	4.75
06/10/2021	9.4	06/15/2021	SHIFT
06/11/2021	7.79	06/16/2021	SHIFT	0.78
06/14/2021	10.17	06/17/2021	SHIFT	0

Answer 5

Hi,

Clearly show the result that you are expecting.