Transfer Multiple Data From Multiple Rows to one Column

[gary2014]

I want to transfer data which are in rows of a Excel File Sheet 1 (Cases Count) and 2 (Login Hours) into a new excel file column. Please help.

Case Count and Daily Time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	MONTHLY CASES COUNT DATA
2	Employee	02-Dec	03-Dec	04-Dec	05-Dec	06-Dec	09-Dec	10-Dec	11-Dec	12-Dec	13-Dec	16-Dec	17-Dec	18-Dec	19-Dec	20-Dec	23-Dec	24-Dec	25-Dec	26-Dec	27-Dec	30-Dec	31-Dec
3	David	15	29	32	45	36	45	15	20	22	29	39	31	19	17	13	19	31	26	47	35	26	35
4	Shane	26	32	26	46	39	48	26	23	26	30	43	33	22	19	15	22	33	15	15	22	29	38
5	Michelle	32	35	29	47	22	24	38	19	15	21	45	35	25	22	26	25	35	26	26	36	26	37
6	Gary	22	38	32	15	23	32	39	22	18	23	15	26	28	26	37	28	26	15	29	38	38	36
7	Chang	36	41	35	26	25	34	26	25	21	25	26	29	31	38	48	31	29	18	32	39	26	26
8	Katlin	38	44	38	29	27	36	18	28	24	27	29	32	34	37	13	26	15	21	35	19	29	38
9	Albert	39	47	37	32	29	38	21	31	27	29	32	35	37	21	15	29	26	24	38	26	32	26
10	Thomas	19	50	36	35	28	40	24	34	30	31	35	38	40	22	17	32	26	27	41	29	32	29
11	Anthony	56	53	35	38	27	42	27	37	33	33	38	41	43	29	19	15	29	30	26	32	35	32
12	Dominic	29	56	34	41	26	44	30	40	36	35	41	44	46	26	21	17	32	32	29	22	38	29
Cases Count

Case Count and Daily Time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	MONTHLY LOGIN HOURS DATA
2	Employee	02-Dec	03-Dec	04-Dec	05-Dec	06-Dec	09-Dec	10-Dec	11-Dec	12-Dec	13-Dec	16-Dec	17-Dec	18-Dec	19-Dec	20-Dec	23-Dec	24-Dec	25-Dec	26-Dec	27-Dec	30-Dec	31-Dec
3	David	7.50	6.36	6.36	7.35	7.29	7.35	7.22	7.09	7.35	6.36	7.11	6.36	6.54	7.50	6.36	6.42	7.22	8.00	6.36	8.00	7.48	6.36
4	Shane	7.52	7.50	7.50	7.09	7.22	7.09	7.09	6.96	7.09	8.00	7.09	8.00	7.50	7.48	8.00	8.00	7.09	6.36	7.50	6.36	7.50	8.00
5	Michelle	7.50	7.48	7.48	8.00	7.09	8.00	8.00	8.00	8.00	6.36	6.96	6.36	8.00	7.35	6.36	6.36	8.00	7.48	8.00	7.48	7.48	6.36
6	Katlin	7.48	7.35	6.36	6.36	8.00	6.36	6.36	6.36	6.36	6.36	8.00	6.36	6.36	7.22	6.36	7.48	6.36	7.35	6.36	7.35	7.35	6.36
7	Albert	7.35	8.00	7.50	8.00	6.36	7.35	7.50	7.48	8.00	7.50	6.36	7.48	7.35	7.09	7.48	7.35	7.50	7.11	7.35	7.35	7.22	7.48
8	Thomas	7.22	6.36	7.48	6.36	7.50	7.22	7.48	7.35	6.36	7.48	7.35	7.11	7.50	8.00	7.35	7.22	7.48	7.09	7.50	7.50	7.09	7.35
9	Gary	7.09	7.48	7.35	7.48	7.48	7.09	7.35	7.22	7.35	7.35	7.22	6.74	7.48	6.36	7.48	7.09	7.35	8.00	7.48	7.48	6.54	7.50
10	Chang	6.96	7.35	7.09	7.35	7.35	8.00	8.00	7.09	7.22	8.00	7.09	6.37	8.00	7.48	7.35	6.96	7.22	6.36	8.00	8.00	7.50	7.48
11	Anthony	8.00	7.22	8.00	7.22	8.00	7.48	6.36	6.00	7.09	6.36	8.00	6.00	7.22	7.35	7.11	6.83	7.09	7.48	6.36	7.50	6.36	8.00
12	Dominic	6.36	7.09	6.36	7.09	6.36	7.35	7.48	5.63	8.00	8.00	6.36	5.63	7.09	7.22	7.09	6.70	6.36	7.35	6.42	6.36	6.42	7.35
13	Names sequence Changed from Sheet 1										Sheet 2
Login Hours

New Consolidated Data.xlsx
	A	B	C	D	E
1	Date	Name	Cases Processed	Time Consumed
2	02-Dec	David	15	7.50	example
3	03-Dec	Shane	29	6.36	example
4	04-Dec	Michelle	32	6.36	example
5	05-Dec	Gary	45	7.35	example
6	06-Dec	Chang	36	7.29	example
7	09-Dec	Katlin	45	7.35	example
8	10-Dec	Albert	15	7.22	example
9	11-Dec	Thomas	20	7.09	example
10	12-Dec	Anthony	22	7.35	example
11	13-Dec	Dominic	29	6.36	example
12
13
14
15
16
17
18
19
20
Sheet1

 

[DRSteele]

This simple INDEX and MATCH formula achieves what you asked. You have a list of all the Dates and a list of each Name, but each Name is for only one Date. I don't know why you would do that - are you sure you've asked the right question? Also, in your exemplar the Cases Processed and Time Consumed columns have looked up the wrong Dates (e.g., Dec 13 Dominic is 8, not 6.36).


C2:

=INDEX(CasesCount!$B$3:$W$12,MATCH(Sheet1!$B2,CasesCount!$A$3:$A$12,0),MATCH($A2,CasesCount!$B$2:$W$2,0))

D2:

=INDEX(LoginHours!$B$3:$W$12,MATCH($B2,LoginHours!$A$3:$A$12,0),MATCH($A2,LoginHours!$B$2:$W$2,0))

 

[alansidman]

This can be accomplished using Power Query. Load both tables independently to PQ. Unpivot the data. Close and load them to a connection only. Perform an inner join on both the names and dates. See the Mcode below:

let
    Source = Table.NestedJoin(Hours, {"Employee", "Attribute"}, Cases, {"Employee", "Attribute"}, "Cases", JoinKind.RightOuter),
    #"Expanded Cases" = Table.ExpandTableColumn(Source, "Cases", {"Value"}, {"Cases.Value"})
in
    #"Expanded Cases"

 

[gary2014]

This simple INDEX and MATCH formula achieves what you asked. You have a list of all the Dates and a list of each Name, but each Name is for only one Date. I don't know why you would do that - are you sure you've asked the right question? Also, in your exemplar the Cases Processed and Time Consumed columns have looked up the wrong Dates (e.g., Dec 13 Dominic is 8, not 6.36).


C2:

=INDEX(CasesCount!$B$3:$W$12,MATCH(Sheet1!$B2,CasesCount!$A$3:$A$12,0),MATCH($A2,CasesCount!$B$2:$W$2,0))

D2:

=INDEX(LoginHours!$B$3:$W$12,MATCH($B2,LoginHours!$A$3:$A$12,0),MATCH($A2,LoginHours!$B$2:$W$2,0))

Hello Dr Steele,

Yes, you were correct.

My apologies, I drafted the New Consolidated Data sheet incorrectly.

The correct form is as follows: I would like to the data to come into new sheet in Transpose form.

Case Count and Daily Time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	MONTHLY CASES COUNT DATA
2	Employee	02-Dec	03-Dec	04-Dec	05-Dec	06-Dec	09-Dec	10-Dec	11-Dec	12-Dec	13-Dec	16-Dec	17-Dec	18-Dec	19-Dec	20-Dec	23-Dec	24-Dec	25-Dec	26-Dec	27-Dec	30-Dec	31-Dec
3	David	15	29	32	45	36	45	15	20	22	29	39	31	19	17	13	19	31	26	47	35	26	35
4	Shane	26	32	26	46	39	48	26	23	26	30	43	33	22	19	15	22	33	15	15	22	29	38
5	Michelle	32	35	29	47	22	24	38	19	15	21	45	35	25	22	26	25	35	26	26	36	26	37
6	Gary	22	38	32	15	23	32	39	22	18	23	15	26	28	26	37	28	26	15	29	38	38	36
7	Chang	36	41	35	26	25	34	26	25	21	25	26	29	31	38	48	31	29	18	32	39	26	26
8	Katlin	38	44	38	29	27	36	18	28	24	27	29	32	34	37	13	26	15	21	35	19	29	38
9	Albert	39	47	37	32	29	38	21	31	27	29	32	35	37	21	15	29	26	24	38	26	32	26
10	Thomas	19	50	36	35	28	40	24	34	30	31	35	38	40	22	17	32	26	27	41	29	32	29
11	Anthony	56	53	35	38	27	42	27	37	33	33	38	41	43	29	19	15	29	30	26	32	35	32
12	Dominic	29	56	34	41	26	44	30	40	36	35	41	44	46	26	21	17	32	32	29	22	38	29
Cases Count

Case Count and Daily Time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	MONTHLY LOGIN HOURS DATA
2	Employee	02-Dec	03-Dec	04-Dec	05-Dec	06-Dec	09-Dec	10-Dec	11-Dec	12-Dec	13-Dec	16-Dec	17-Dec	18-Dec	19-Dec	20-Dec	23-Dec	24-Dec	25-Dec	26-Dec	27-Dec	30-Dec	31-Dec
3	David	7.50	6.36	6.36	7.35	7.29	7.35	7.22	7.09	7.35	6.36	7.11	6.36	6.54	7.50	6.36	6.42	7.22	8.00	6.36	8.00	7.48	6.36
4	Shane	7.52	7.50	7.50	7.09	7.22	7.09	7.09	6.96	7.09	8.00	7.09	8.00	7.50	7.48	8.00	8.00	7.09	6.36	7.50	6.36	7.50	8.00
5	Michelle	7.50	7.48	7.48	8.00	7.09	8.00	8.00	8.00	8.00	6.36	6.96	6.36	8.00	7.35	6.36	6.36	8.00	7.48	8.00	7.48	7.48	6.36
6	Katlin	7.48	7.35	6.36	6.36	8.00	6.36	6.36	6.36	6.36	6.36	8.00	6.36	6.36	7.22	6.36	7.48	6.36	7.35	6.36	7.35	7.35	6.36
7	Albert	7.35	8.00	7.50	8.00	6.36	7.35	7.50	7.48	8.00	7.50	6.36	7.48	7.35	7.09	7.48	7.35	7.50	7.11	7.35	7.35	7.22	7.48
8	Thomas	7.22	6.36	7.48	6.36	7.50	7.22	7.48	7.35	6.36	7.48	7.35	7.11	7.50	8.00	7.35	7.22	7.48	7.09	7.50	7.50	7.09	7.35
9	Gary	7.09	7.48	7.35	7.48	7.48	7.09	7.35	7.22	7.35	7.35	7.22	6.74	7.48	6.36	7.48	7.09	7.35	8.00	7.48	7.48	6.54	7.50
10	Chang	6.96	7.35	7.09	7.35	7.35	8.00	8.00	7.09	7.22	8.00	7.09	6.37	8.00	7.48	7.35	6.96	7.22	6.36	8.00	8.00	7.50	7.48
11	Anthony	8.00	7.22	8.00	7.22	8.00	7.48	6.36	6.00	7.09	6.36	8.00	6.00	7.22	7.35	7.11	6.83	7.09	7.48	6.36	7.50	6.36	8.00
12	Dominic	6.36	7.09	6.36	7.09	6.36	7.35	7.48	5.63	8.00	8.00	6.36	5.63	7.09	7.22	7.09	6.70	6.36	7.35	6.42	6.36	6.42	7.35
13	Names sequence Changed from Sheet 1										Sheet 2
Login Hours

New Consolidated Data.xlsx
	A	B	C	D	E
1	Date	Name	Cases Processed	Time Consumed
2	02-Dec	David	15	7.50	example (Transpose)
3	03-Dec	David	29	6.36	example (Transpose)
4	04-Dec	David	32	6.36	example (Transpose)
5	05-Dec	David	45	7.35	example (Transpose)
6	06-Dec	David	36	7.29	example (Transpose)
7	09-Dec	David	45	7.35	example (Transpose)
8	10-Dec	David	15	7.22	example (Transpose)
9	11-Dec	David	20	7.09	example (Transpose)
10	12-Dec	David	22	7.35	example (Transpose)
11	13-Dec	David	29	6.36	example (Transpose)
12	16-Dec	David	39	7.11	example (Transpose)
13	17-Dec	David	31	6.36	example (Transpose)
14	18-Dec	David	19	6.54	example (Transpose)
15	19-Dec	David	17	7.50	example (Transpose)
16	…… till 31-Dec
17	02-Dec	Shane	26	7.52
18	03-Dec	Shane	32	7.50
19	04-Dec	Shane	26	7.50
20	05-Dec	Shane	46	7.09
21	06-Dec	Shane	39	7.22
22	09-Dec	Shane	48	7.09
23	10-Dec	Shane	26	7.09
24	11-Dec	Shane	23	6.96
25	12-Dec	Shane	26	7.09
26	13-Dec	Shane	30	8.00
27	…… till 31-Dec
28
Sheet1

 

[gary2014]

This can be accomplished using Power Query. Load both tables independently to PQ. Unpivot the data. Close and load them to a connection only. Perform an inner join on both the names and dates. See the Mcode below:

let
    Source = Table.NestedJoin(Hours, {"Employee", "Attribute"}, Cases, {"Employee", "Attribute"}, "Cases", JoinKind.RightOuter),
    #"Expanded Cases" = Table.ExpandTableColumn(Source, "Cases", {"Value"}, {"Cases.Value"})
in
    #"Expanded Cases"

Hello Alansidman,

My apologies, I drafted the New Consolidated Data sheet incorrectly.

The correct form is as follows: I would like to the data to come into new sheet in Transpose form.

Case Count and Daily Time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	MONTHLY CASES COUNT DATA
2	Employee	02-Dec	03-Dec	04-Dec	05-Dec	06-Dec	09-Dec	10-Dec	11-Dec	12-Dec	13-Dec	16-Dec	17-Dec	18-Dec	19-Dec	20-Dec	23-Dec	24-Dec	25-Dec	26-Dec	27-Dec	30-Dec	31-Dec
3	David	15	29	32	45	36	45	15	20	22	29	39	31	19	17	13	19	31	26	47	35	26	35
4	Shane	26	32	26	46	39	48	26	23	26	30	43	33	22	19	15	22	33	15	15	22	29	38
5	Michelle	32	35	29	47	22	24	38	19	15	21	45	35	25	22	26	25	35	26	26	36	26	37
6	Gary	22	38	32	15	23	32	39	22	18	23	15	26	28	26	37	28	26	15	29	38	38	36
7	Chang	36	41	35	26	25	34	26	25	21	25	26	29	31	38	48	31	29	18	32	39	26	26
8	Katlin	38	44	38	29	27	36	18	28	24	27	29	32	34	37	13	26	15	21	35	19	29	38
9	Albert	39	47	37	32	29	38	21	31	27	29	32	35	37	21	15	29	26	24	38	26	32	26
10	Thomas	19	50	36	35	28	40	24	34	30	31	35	38	40	22	17	32	26	27	41	29	32	29
11	Anthony	56	53	35	38	27	42	27	37	33	33	38	41	43	29	19	15	29	30	26	32	35	32
12	Dominic	29	56	34	41	26	44	30	40	36	35	41	44	46	26	21	17	32	32	29	22	38	29
Cases Count

Case Count and Daily Time.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	MONTHLY LOGIN HOURS DATA
2	Employee	02-Dec	03-Dec	04-Dec	05-Dec	06-Dec	09-Dec	10-Dec	11-Dec	12-Dec	13-Dec	16-Dec	17-Dec	18-Dec	19-Dec	20-Dec	23-Dec	24-Dec	25-Dec	26-Dec	27-Dec	30-Dec	31-Dec
3	David	7.50	6.36	6.36	7.35	7.29	7.35	7.22	7.09	7.35	6.36	7.11	6.36	6.54	7.50	6.36	6.42	7.22	8.00	6.36	8.00	7.48	6.36
4	Shane	7.52	7.50	7.50	7.09	7.22	7.09	7.09	6.96	7.09	8.00	7.09	8.00	7.50	7.48	8.00	8.00	7.09	6.36	7.50	6.36	7.50	8.00
5	Michelle	7.50	7.48	7.48	8.00	7.09	8.00	8.00	8.00	8.00	6.36	6.96	6.36	8.00	7.35	6.36	6.36	8.00	7.48	8.00	7.48	7.48	6.36
6	Katlin	7.48	7.35	6.36	6.36	8.00	6.36	6.36	6.36	6.36	6.36	8.00	6.36	6.36	7.22	6.36	7.48	6.36	7.35	6.36	7.35	7.35	6.36
7	Albert	7.35	8.00	7.50	8.00	6.36	7.35	7.50	7.48	8.00	7.50	6.36	7.48	7.35	7.09	7.48	7.35	7.50	7.11	7.35	7.35	7.22	7.48
8	Thomas	7.22	6.36	7.48	6.36	7.50	7.22	7.48	7.35	6.36	7.48	7.35	7.11	7.50	8.00	7.35	7.22	7.48	7.09	7.50	7.50	7.09	7.35
9	Gary	7.09	7.48	7.35	7.48	7.48	7.09	7.35	7.22	7.35	7.35	7.22	6.74	7.48	6.36	7.48	7.09	7.35	8.00	7.48	7.48	6.54	7.50
10	Chang	6.96	7.35	7.09	7.35	7.35	8.00	8.00	7.09	7.22	8.00	7.09	6.37	8.00	7.48	7.35	6.96	7.22	6.36	8.00	8.00	7.50	7.48
11	Anthony	8.00	7.22	8.00	7.22	8.00	7.48	6.36	6.00	7.09	6.36	8.00	6.00	7.22	7.35	7.11	6.83	7.09	7.48	6.36	7.50	6.36	8.00
12	Dominic	6.36	7.09	6.36	7.09	6.36	7.35	7.48	5.63	8.00	8.00	6.36	5.63	7.09	7.22	7.09	6.70	6.36	7.35	6.42	6.36	6.42	7.35
13	Names sequence Changed from Sheet 1										Sheet 2
Login Hours

New Consolidated Data.xlsx
	A	B	C	D	E
1	Date	Name	Cases Processed	Time Consumed
2	02-Dec	David	15	7.50	example (Transpose)
3	03-Dec	David	29	6.36	example (Transpose)
4	04-Dec	David	32	6.36	example (Transpose)
5	05-Dec	David	45	7.35	example (Transpose)
6	06-Dec	David	36	7.29	example (Transpose)
7	09-Dec	David	45	7.35	example (Transpose)
8	10-Dec	David	15	7.22	example (Transpose)
9	11-Dec	David	20	7.09	example (Transpose)
10	12-Dec	David	22	7.35	example (Transpose)
11	13-Dec	David	29	6.36	example (Transpose)
12	16-Dec	David	39	7.11	example (Transpose)
13	17-Dec	David	31	6.36	example (Transpose)
14	18-Dec	David	19	6.54	example (Transpose)
15	19-Dec	David	17	7.50	example (Transpose)
16	…… till 31-Dec
17	02-Dec	Shane	26	7.52
18	03-Dec	Shane	32	7.50
19	04-Dec	Shane	26	7.50
20	05-Dec	Shane	46	7.09
21	06-Dec	Shane	39	7.22
22	09-Dec	Shane	48	7.09
23	10-Dec	Shane	26	7.09
24	11-Dec	Shane	23	6.96
25	12-Dec	Shane	26	7.09
26	13-Dec	Shane	30	8.00
27	…… till 31-Dec
28
Sheet1

 

[alansidman]

Take the results supplied and then filter them on the Name first and the date second. Should provide you with the expected results.