Transfer multi-column data to 2 column

[ExcelNewbie2020]

Sirs,

I have multi-column data which was arranged horizontally and vertically. If it is possible, the multi-column can be transferred to 2 columns to be arranged vertically.

For better understanding of my issue. please find the attached sample file.. Thank you in advance.

sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1																				RESULT
2		INPUT DATA																		Date	Amount
3	Name	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount			05-07-19	2,000.00	FORMULA HERE
4	Name 1	05-07-19	2,000.00			10-23-19	3,000.00	04-05-19	2,000.00	05-08-19	6,000.00			07-08-19	9,500.00	07-30-19	10,000.00			07-07-19	2,500.00
5	Name 2	07-07-19	2,500.00							06-22-19	2,500.00	11-21-19	2,500.00	08-15-19	9,500.00					03-31-19	2,000.00
6	Name 3			03-31-19	2,000.00	12-11-19	2,000.00	01-06-19	6,000.00			07-21-19	2,500.00			10-10-19	2,500.00
7	Name 4			12-24-19	2,000.00					12-19-19	2,500.00	08-26-19	2,500.00			07-12-19	2,500.00
8	Name 5	06-17-19	2,500.00	06-01-19	2,000.00					10-11-19	2,500.00	08-23-19	2,500.00	11-13-19	10,000.00	12-26-19	2,500.00
9	Name 6	12-15-19	5,000.00	09-30-19	3,000.00	09-28-19	6,000.00	02-25-19	2,500.00			01-19-19	10,000.00	08-15-19	10,000.00
10	Name 7	05-16-19	5,000.00					04-01-19	2,500.00			11-17-19	10,000.00			05-19-19	6,000.00
11	Name 8			04-06-19	3,000.00	03-14-19	6,000.00	10-30-19	2,500.00	07-05-19	6,000.00			05-01-19	10,000.00
12
13
14
15			*ALL DATA IN BLUE COLOR CELLS (INPUT DATA) WILL AUTOMATICALLY TRANSFER TO RESULT COLUMN
16			* MUST BE GROUP BY DATE & AMOUNT
17
18
19
20
21
Sheet2

 

[DanteAmor]

Here is a macro for you to consider:

Sub Transfer_Multi_Column()
  Dim dic As Object, i As Long, j As Long
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 4 To Range("A" & Rows.Count).End(3).Row   'initial row 4
    For j = 2 To Columns("Q").Column Step 2         'initial column 2
      If Cells(i, j).Value <> "" Then dic(Cells(i, j).Value) = dic(Cells(i, j).Value) + Cells(i, j + 1).Value
    Next
  Next
  Range("T3").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
End Sub

 

[ExcelNewbie2020]

Here is a macro for you to consider:

Sub Transfer_Multi_Column()
  Dim dic As Object, i As Long, j As Long
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 4 To Range("A" & Rows.Count).End(3).Row   'initial row 4
    For j = 2 To Columns("Q").Column Step 2         'initial column 2
      If Cells(i, j).Value <> "" Then dic(Cells(i, j).Value) = dic(Cells(i, j).Value) + Cells(i, j + 1).Value
    Next
  Next
  Range("T3").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
End Sub

thanks man, it works.. I also add a code that every time a cell range was changed the macro will automatically run..

 

[Peter_SSs]

If your data is not too big and you were interested in a formula approach you can do it with just these two formulas in T3:U3 and the other results will automatically spill down the other required rows. I have included a sort to put the dates in chronological order.

ExcelNewbie2020.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	INPUT DATA																		Date	Amount
3	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount			6/01/2019	6000
4	7/05/2019	2000			23/10/2019	3000	5/04/2019	2000	8/05/2019	6000			8/07/2019	9500	30/07/2019	10000			19/01/2019	10000
5	7/07/2019	2500							22/06/2019	2500	21/11/2019	2500	15/08/2019	9500					25/02/2019	2500
6			31/03/2019	2000	11/12/2019	2000	6/01/2019	6000			21/07/2019	2500			10/10/2019	2500			14/03/2019	6000
7			24/12/2019	2000					19/12/2019	2500	26/08/2019	2500			12/07/2019	2500			31/03/2019	2000
8	17/06/2019	2500	1/06/2019	2000					11/10/2019	2500	23/08/2019	2500	13/11/2019	10000	26/12/2019	2500			1/04/2019	2500
9	15/12/2019	5000	30/09/2019	3000	28/09/2019	6000	25/02/2019	2500			19/01/2019	10000	15/08/2019	10000					5/04/2019	2000
10	16/05/2019	5000					1/04/2019	2500			17/11/2019	10000			19/05/2019	6000			6/04/2019	3000
11			6/04/2019	3000	14/03/2019	6000	30/10/2019	2500	5/07/2019	6000			1/05/2019	10000					1/05/2019	10000
12																			7/05/2019	2000
13																			8/05/2019	6000
14																			16/05/2019	5000
15																			19/05/2019	6000
16																			1/06/2019	2000
17																			17/06/2019	2500
18																			22/06/2019	2500
19																			5/07/2019	6000
20																			7/07/2019	2500
21																			8/07/2019	9500
22																			12/07/2019	2500
23																			21/07/2019	2500
24																			30/07/2019	10000
25																			15/08/2019	19500
26																			23/08/2019	2500
27																			26/08/2019	2500
28																			28/09/2019	6000
29																			30/09/2019	3000
30																			10/10/2019	2500
31																			11/10/2019	2500
32																			23/10/2019	3000
33																			30/10/2019	2500
34																			13/11/2019	10000
35																			17/11/2019	10000
36																			21/11/2019	2500
37																			11/12/2019	2000
38																			15/12/2019	5000
39																			19/12/2019	2500
40																			24/12/2019	2000
41																			26/12/2019	2500
42
Date Summary

Cell Formulas
Range	Formula
T3:T41	T3	=LET(f,FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,IF(B3:Q3="Date",B4:Q11,""))&"</c></p>","//c"),SORT(UNIQUE(FILTER(f,f>0))))
U3:U41	U3	=SUMIFS(C4:Q11,B4:P11,T3#)
Dynamic array formulas.

 

[ExcelNewbie2020]

If your data is not too big and you were interested in a formula approach you can do it with just these two formulas in T3:U3 and the other results will automatically spill down the other required rows. I have included a sort to put the dates in chronological order.

ExcelNewbie2020.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	INPUT DATA																		Date	Amount
3	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount	Date	Amount			6/01/2019	6000
4	7/05/2019	2000			23/10/2019	3000	5/04/2019	2000	8/05/2019	6000			8/07/2019	9500	30/07/2019	10000			19/01/2019	10000
5	7/07/2019	2500							22/06/2019	2500	21/11/2019	2500	15/08/2019	9500					25/02/2019	2500
6			31/03/2019	2000	11/12/2019	2000	6/01/2019	6000			21/07/2019	2500			10/10/2019	2500			14/03/2019	6000
7			24/12/2019	2000					19/12/2019	2500	26/08/2019	2500			12/07/2019	2500			31/03/2019	2000
8	17/06/2019	2500	1/06/2019	2000					11/10/2019	2500	23/08/2019	2500	13/11/2019	10000	26/12/2019	2500			1/04/2019	2500
9	15/12/2019	5000	30/09/2019	3000	28/09/2019	6000	25/02/2019	2500			19/01/2019	10000	15/08/2019	10000					5/04/2019	2000
10	16/05/2019	5000					1/04/2019	2500			17/11/2019	10000			19/05/2019	6000			6/04/2019	3000
11			6/04/2019	3000	14/03/2019	6000	30/10/2019	2500	5/07/2019	6000			1/05/2019	10000					1/05/2019	10000
12																			7/05/2019	2000
13																			8/05/2019	6000
14																			16/05/2019	5000
15																			19/05/2019	6000
16																			1/06/2019	2000
17																			17/06/2019	2500
18																			22/06/2019	2500
19																			5/07/2019	6000
20																			7/07/2019	2500
21																			8/07/2019	9500
22																			12/07/2019	2500
23																			21/07/2019	2500
24																			30/07/2019	10000
25																			15/08/2019	19500
26																			23/08/2019	2500
27																			26/08/2019	2500
28																			28/09/2019	6000
29																			30/09/2019	3000
30																			10/10/2019	2500
31																			11/10/2019	2500
32																			23/10/2019	3000
33																			30/10/2019	2500
34																			13/11/2019	10000
35																			17/11/2019	10000
36																			21/11/2019	2500
37																			11/12/2019	2000
38																			15/12/2019	5000
39																			19/12/2019	2500
40																			24/12/2019	2000
41																			26/12/2019	2500
42
Date Summary

Cell Formulas
Range	Formula
T3:T41	T3	=LET(f,FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,IF(B3:Q3="Date",B4:Q11,""))&"</c></p>","//c"),SORT(UNIQUE(FILTER(f,f>0))))
U3:U41	U3	=SUMIFS(C4:Q11,B4:P11,T3#)
Dynamic array formulas.

interesting formula.. it works like a charm...a simple approach.. thank you very much

 

[Peter_SSs]

interesting formula.. it works like a charm...a simple approach.. thank you very much

You're very welcome. Thanks for the follow-up.