Group data from column into separate rows horizontally

[chhantelravi]

Hi Everyone,

I have a data file which looks like below attachment.

I need to modify it as per my report format which is attached below.

Its really time consuming and hectic for huge files. So can anyone have any idea how to do this?
I would really appreciate help from excel geniuses..
Thanks
Ravi

 

[Peter_SSs]

Welcome to the MrExcel board

How does XYZ co magically turn into Mega Foods And beverage Pvt. Ltd in row 8?
Similarly ABC Co -> Four Square International Pvt. Ltd. in row 11?

 

[chhantelravi]

Welcome to the MrExcel board

How does XYZ co magically turn into Mega Foods And beverage Pvt. Ltd in row 8?
Similarly ABC Co -> Four Square International Pvt. Ltd. in row 11?

My bad...
Assume they are same company...
Do you know how to show amount in separate column for each entry???

 

[Peter_SSs]

Do you know how to show amount in separate column for each entry???

You haven't actually explained what triggers moving to a new column but see if this helps. If not please give more explanation & examples.

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Then you wouldn't get very simplified data like I have used below.

Enter the first formula in E2 and it should spill down that column in your table. Then select that column of formulas and drag right into the other columns.

chhantelravi 2020-08-12 1.xlsm
	A	B	C	D	E	F	G	H
1	Doc #	Desc	Debit	Credit	Amount 1	Amount 2	Amount 3	Amount 4
2		Desc 1	1		1
3		Desc 2	2		2
4		Desc 3		3	-3
5		Desc 4 (15%)		4	-4
6		Desc 5	5		5
7		Desc 6 (13%)	6			6
8		Desc 7		7		-7
9		Desc 8	8			8
10		Desc 9 (13%)	9				9
11		Desc 10		10			-10
12		Desc 11	11				11
Sheet1

Cell Formulas
Range	Formula
E2:H12	E2	=IF(MAX(COUNTIF($B$1:$B2,"*%)"),1)=COLUMNS($E:E),IF($D2="",1,-1)*SUM($C2:$D2),"")

 

[chhantelravi]

You haven't actually explained what triggers moving to a new column but see if this helps. If not please give more explanation & examples.

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Then you wouldn't get very simplified data like I have used below.

Enter the first formula in E2 and it should spill down that column in your table. Then select that column of formulas and drag right into the other columns.

chhantelravi 2020-08-12 1.xlsm
	A	B	C	D	E	F	G	H
1	Doc #	Desc	Debit	Credit	Amount 1	Amount 2	Amount 3	Amount 4
2		Desc 1	1		1
3		Desc 2	2		2
4		Desc 3		3	-3
5		Desc 4 (15%)		4	-4
6		Desc 5	5		5
7		Desc 6 (13%)	6			6
8		Desc 7		7		-7
9		Desc 8	8			8
10		Desc 9 (13%)	9				9
11		Desc 10		10			-10
12		Desc 11	11				11
Sheet1

Cell Formulas
Range	Formula
E2:H12	E2	=IF(MAX(COUNTIF($B$1:$B2,"*%)"),1)=COLUMNS($E:E),IF($D2="",1,-1)*SUM($C2:$D2),"")

 

[chhantelravi]

Excel working.xlsx
	A	B	C	D	E	F	G	H	I
1		Original format				Required Format
2	Vr ref	Account Name	Dr. Amount	Cr. Amount		Account Name	transaction1	transaction2	transaction3
3	1	Labour Charges	266,111.11			Labour Charges	266,111.11
4	1	Labour Charges Payable		263,450.00		Labour Charges Payable	(263,450.00)
5	1	Withholding Tax - SST (1%)		2,661.11		Withholding Tax - SST (1%)	(2,661.11)
6	2	Intercompany Transactions - SMSK- TIGER	60,000.00			Intercompany Transactions - SMSK- TIGER		60,000.00
7	2	TDS Receivables	1,500.00			TDS Receivables		1,500.00
8	2	Sundry Debtros Control Account		61,500.00		Sundry Debtros Control Account		(61,500.00)
9	3	Intercompany Transactions	190,000.00			Intercompany Transactions			190,000.00
10	3	TDS Receivables	30,000.00			TDS Receivables			30,000.00
11	3	Sundry Debtros Control Account		220,000.00		Sundry Debtros Control Account			(220,000.00)
12	4	Employee Salary	100,000.00
13	4	PF - Employers Contribution	10,000.00
14	4	Gratuity	10,000.00
15	4	Accomodation Allowance	50,000.00
16	4	Salary Payable		115,000.00
17	4	Withholding Tax - SST (1%)		10,000.00
18	4	Withholding Tax - Salary (15%)		25,000.00
19	4	PF Payable		10,000.00
20	4	Gratuity Payable		10,000.00
21	5	NSBL-CA-BHW	60,000.00
22	5	PCBL - BHW_Account		60,000.00
23	6	NIBL- Current Account	5,000.00
24	6	PCBL - BHW_Account		50,000.00
25	7	Salary Payable	50,000.00
26	7	NSBL-CA-BHW		50,000.00
27	8	Salary Payable	33,000.00
28	8	NIBL- Current Account		33,000.00
29	9	Salary Payable	2,449,066.30
30	9	PCBL - BHW_Account		2,449,066.30
31	10	Salary Payable	2,667,147.00
32	10	HBL - USD Account		2,560,460.54
33	10	Foreign Exchange Fluctuation Gain		106,686.46
34	11	Intercompany Transactions	500,000.00
35	11	TDS Receivables	6,000.00
36	11	Rental Income		506,000.00
37	12	Sundry Debtros Control Account	40,000.00
38	12	Income from Space Rental_Lobby Lounge		40,000.00
39	13	Sundry Debtros Control Account	12,000.00
40	13	Income from Sale of Waste & Scrap		12,000.00
41	14	Interest on Loan	110,000.00
42	14	Interest Payable		110,000.00
43	16	Sercurity charge	1,133,661.29
44	16	Input VAT (13%)	147,375.97
45	16	ABC Co		1,281,037.26
46	17	ABC Co	17,005.00
47	17	Withholding Tax - Contractual Payments		17,005.00
48	18	Sales Commission	422,870.00
49	18	Sales Commission	321,674.00
50	18	Business Promotion Expense	423,427.00
51	18	82022-Lease Rental - Vehicle	312,233.00
52	18	82022-Lease Rental - Vehicle	46,449.00
53	18	82022-Lease Rental - Vehicle	9,111.00
54	18	82022-Lease Rental - Vehicle	166,401.00
55	18	82033-Internet Expenses	185,851.00
56	18	82041-Electricity Expense	189,860.00
57	18	82060-Festival & Pooja Expenses	60,834.00
58	18	82070-Pest Control Services	476,261.00
59	18	82076-Program & Function Expense	474,013.00
60	18	Gardening Expenses	306,051.00
61	18	Financial Consultancy	153,794.00
62	18	Legal Consultancy	386,301.00
63	18	SPA fees	59,350.00
64	18	Management Service Fees	75,079.00
65	18	Provision for Expenses		4,069,559.00
66	19	Financial Audit Fee	45,833.33
67	19	Provision for audit Fees		45,833.33
68	20	Employee Dashain Bonus	100,000.00
69	20	Provision for Festival Bonus		100,000.00
Sheet1

above attached file is I am working on.. I tried your previous suggestion but it didn't work.
Can you look the file and suggest?
Is it possible perform with power query? If yes, Pls advise.

Thanks

 

[Peter_SSs]

You haven't actually explained what triggers moving to a new column ..

You still haven't explained that but looking again I see that it is likely when the value in column a changes?

If so, here is one way with a helper column that could be hidden.

chhantelravi 2020-08-12 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Original format				Required Format
2	Vr ref	Account Name	Dr. Amount	Cr. Amount		Account Name		transaction1	transaction2	transaction3
3	1	Labour Charges	266111.1111			Labour Charges	1	266111.11
4	1	Labour Charges Payable		263450		Labour Charges Payable	1	(263450.00)
5	1	Withholding Tax - SST (1%)		2661.111111		Withholding Tax - SST (1%)	1	(2661.11)
6	2	Intercompany Transactions - SMSK- TIGER	60000			Intercompany Transactions - SMSK- TIGER	2		60000.00
7	2	TDS Receivables	1500			TDS Receivables	2		1500.00
8	2	Sundry Debtros Control Account		61500		Sundry Debtros Control Account	2		(61500.00)
9	3	Intercompany Transactions	190000			Intercompany Transactions	3			190000.00
10	3	TDS Receivables	30000			TDS Receivables	3			30000.00
11	3	Sundry Debtros Control Account		220000		Sundry Debtros Control Account	3			(220000.00)
12	4	Employee Salary	100000			Employee Salary	4
13	4	PF - Employers Contribution	10000			PF - Employers Contribution	4
14	4	Gratuity	10000			Gratuity	4
Sheet2

Cell Formulas
Range	Formula
F3:F14	F3	=B3
G3:G14	G3	=G2+(A3<>A2)
H3:J14	H3	=IF(COLUMNS($H:H)=$G3,IF($D3="",1,-1)*SUM($C3:$D3),"")

Is it possible perform with power query? If yes, Pls advise.

It is, but I don't answer forum questions about that. There are others who do and I'm sure at least one of them will chime in with a PQ solution.

 

[chhantelravi]

Thank you very much...
You have made my day..

 

[Peter_SSs]

Thank you very much...
You have made my day..

You are very welcome. Thanks for the follow-up.

 

[Peter_SSs]

Just looked at this again and not sure why I made it like that. The formula for columns H, I, J, .. could be simpler ..

chhantelravi 2020-08-12 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Original format				Required Format
2	Vr ref	Account Name	Dr. Amount	Cr. Amount		Account Name		transaction1	transaction2	transaction3
3	1	Labour Charges	266111.1111			Labour Charges	1	266111.11
4	1	Labour Charges Payable		263450		Labour Charges Payable	1	(263450.00)
5	1	Withholding Tax - SST (1%)		2661.111111		Withholding Tax - SST (1%)	1	(2661.11)
6	2	Intercompany Transactions - SMSK- TIGER	60000			Intercompany Transactions - SMSK- TIGER	2		60000.00
7	2	TDS Receivables	1500			TDS Receivables	2		1500.00
8	2	Sundry Debtros Control Account		61500		Sundry Debtros Control Account	2		(61500.00)
9	3	Intercompany Transactions	190000			Intercompany Transactions	3			190000.00
10	3	TDS Receivables	30000			TDS Receivables	3			30000.00
11	3	Sundry Debtros Control Account		220000		Sundry Debtros Control Account	3			(220000.00)
Sheet3

Cell Formulas
Range	Formula
F3:F11	F3	=B3
G3:G11	G3	=G2+(A3<>A2)
H3:J11	H3	=IF(COLUMNS($H:H)=$G3,IF($D3="",$C3,-$D3),"")

.. or if columns C:D are not formulas ..

chhantelravi 2020-08-12 1.xlsm
	A	B	C	D	E	F	G	H	I	J
2	Vr ref	Account Name	Dr. Amount	Cr. Amount		Account Name		transaction1	transaction2	transaction3
3	1	Labour Charges	266111.1111			Labour Charges	1	266111.11
4	1	Labour Charges Payable		263450		Labour Charges Payable	1	(263450.00)
5	1	Withholding Tax - SST (1%)		2661.111111		Withholding Tax - SST (1%)	1	(2661.11)
6	2	Intercompany Transactions - SMSK- TIGER	60000			Intercompany Transactions - SMSK- TIGER	2		60000.00
7	2	TDS Receivables	1500			TDS Receivables	2		1500.00
8	2	Sundry Debtros Control Account		61500		Sundry Debtros Control Account	2		(61500.00)
9	3	Intercompany Transactions	190000			Intercompany Transactions	3			190000.00
10	3	TDS Receivables	30000			TDS Receivables	3			30000.00
11	3	Sundry Debtros Control Account		220000		Sundry Debtros Control Account	3			(220000.00)
Sheet4

Cell Formulas
Range	Formula
F3:F11	F3	=B3
G3:G11	G3	=G2+(A3<>A2)
H3:J11	H3	=IF(COLUMNS($H:H)=$G3,$C3-$D3,"")