Complex Grouping

Sunil Pinto

New Member
Joined
Jul 31, 2024
Messages
24
Office Version
  1. 2016
I have amounts in column A and group types in column B. In column C, I need to create a formula to categorize the data based on the group type and amount. The challenge is to include sub-grouping for Payables, Related Parties, and Trade Receivables. How can I develop a single formula in column C that can be dragged down for all rows?

Details are attached below

https://1drv.ms/x/c/4ac6cf5129cd6403/ESSrnpTT6uhFnn_Gaajr3BIBVNv96U-5KHLy12ABSNrd4g?e=CiDhCB
 
Last edited:
There are a couple of reasons that you have not received a resonse much earlier.

1.
categorize the data based on the group type and amount.
You have not given any logic of how group type and amount determine the category. You are obviously familiar with the data and what it means, but helpers here likely have no idea so you need to fill us in. ;)

2.
Many of the helpers here choose not to download files from other sites or, due to security restrictions at their workplace, are unable to download such files.
You will generally get more potential helpers & faster if you explain your problem clearly in words and, if needed, post a small (copyable) screen shot or two directly in your post. My signature block at the bottom of this post has help regarding that - see the XL2BB link.


Anyway, see if this is what you want.

Excel Task.xlsx
ABC
7 Amount GroupFormula-Result
8(498.31)BorrowingsBorrowings
9(850.00)BorrowingsBorrowings
10(656.25)BorrowingsBorrowings
11(758.77)PayablesTrade Payables
12(153.00)PayablesTrade Payables
132,021.74AssetsAssets
14(1,525.00)PayablesTrade Payables
15(218.75)PayablesTrade Payables
16697.70AssetsAssets
172,424.00PayablesOther Receivables
181,965.04PayablesOther Receivables
19(1,260.00)PayablesTrade Payables
201,564.67PayablesOther Receivables
21(315.00)PayablesTrade Payables
221,386.00Related PartiesDue from Related Parties
23262.50Related PartiesDue from Related Parties
24(682.50)Related PartiesDue to Related Parties
25(472.50)Related PartiesDue to Related Parties
26(815.00)Related PartiesDue to Related Parties
27(584.00)Related PartiesDue to Related Parties
281,940.00Related PartiesDue from Related Parties
2975.00Related PartiesDue from Related Parties
301,883.00PayablesOther Receivables
31353.63PayablesOther Receivables
32964.00PayablesOther Receivables
33102.42Cash & BankCash & Bank
34146.00Cash & BankCash & Bank
352,696.57Cash & BankCash & Bank
36(244.20)Trade ReceivablesOther Payables
37622.81Trade ReceivablesTrade Payables
38(69.00)Trade ReceivablesOther Payables
391,260.00Trade ReceivablesTrade Payables
40(1,495.00)Trade ReceivablesOther Payables
41(1,500.00)PayablesTrade Payables
42595.11Trade ReceivablesTrade Payables
43(580.75)PayablesTrade Payables
4475.00Trade ReceivablesTrade Payables
45(269.66)PayablesTrade Payables
46(960.75)PayablesTrade Payables
47(1,342.12)PayablesTrade Payables
48607.15Trade ReceivablesTrade Payables
49488.00Trade ReceivablesTrade Payables
50(110.00)Trade ReceivablesOther Payables
51(446.25)PayablesTrade Payables
52(39.07)Trade ReceivablesOther Payables
53(52.11)Trade ReceivablesOther Payables
54(31.84)Trade ReceivablesOther Payables
55720.00PayablesOther Receivables
56(787.50)PayablesTrade Payables
57(525.00)PayablesTrade Payables
581,017.54Trade ReceivablesTrade Payables
59(667.86)PayablesTrade Payables
601,917.57Trade ReceivablesTrade Payables
61(603.75)PayablesTrade Payables
62(1,178.10)PayablesTrade Payables
63(692.75)PayablesTrade Payables
642,517.00Related PartiesDue from Related Parties
65609.78Trade ReceivablesTrade Payables
66(1,050.00)PayablesTrade Payables
67(259.39)PayablesTrade Payables
68(787.50)PayablesTrade Payables
69807.43PayablesOther Receivables
70717.48PayablesOther Receivables
71823.32PayablesOther Receivables
72(525.00)Trade ReceivablesOther Payables
7395.00Trade ReceivablesTrade Payables
74(315.00)PayablesTrade Payables
75102.83PayablesOther Receivables
76682.80Related PartiesDue from Related Parties
77(455.00)BorrowingsBorrowings
78(598.50)BorrowingsBorrowings
79(577.50)BorrowingsBorrowings
80(120.00)BorrowingsBorrowings
81(488.00)BorrowingsBorrowings
82(1,460.17)BorrowingsBorrowings
83(396.25)BorrowingsBorrowings
84(504.00)BorrowingsBorrowings
85(1,510.00)BorrowingsBorrowings
861,525.00Cash & BankCash & Bank
87(130.00)BorrowingsBorrowings
88(571.57)BorrowingsBorrowings
89(540.75)BorrowingsBorrowings
90775.00Cash & BankCash & Bank
91(766.50)BorrowingsBorrowings
92566.00Cash & BankCash & Bank
931,009.97Cash & BankCash & Bank
941,827.00Cash & BankCash & Bank
95(225.23)BorrowingsBorrowings
96(279.98)BorrowingsBorrowings
97(247.00)BorrowingsBorrowings
98(1,310.00)BorrowingsBorrowings
991,387.62Cash & BankCash & Bank
100128.90Cash & BankCash & Bank
1011,482.52Cash & BankCash & Bank
31.12.2024 (2)
Cell Formulas
RangeFormula
C8:C101C8=IF(B8="Payables",IF(A8<0,"Trade Payables","Other Receivables"),IF(B8="Related parties","Due "&IF(A8<0,"to","from")&" Related Parties",IF(B8="Trade Receivables",IF(A8<0,"Other","Trade")&" Payables",B8)))
 
Upvote 0
Solution
There are a couple of reasons that you have not received a resonse much earlier.

1.

You have not given any logic of how group type and amount determine the category. You are obviously familiar with the data and what it means, but helpers here likely have no idea so you need to fill us in. ;)

2.
Many of the helpers here choose not to download files from other sites or, due to security restrictions at their workplace, are unable to download such files.
You will generally get more potential helpers & faster if you explain your problem clearly in words and, if needed, post a small (copyable) screen shot or two directly in your post. My signature block at the bottom of this post has help regarding that - see the XL2BB link.


Anyway, see if this is what you want.

Excel Task.xlsx
ABC
7 Amount GroupFormula-Result
8(498.31)BorrowingsBorrowings
9(850.00)BorrowingsBorrowings
10(656.25)BorrowingsBorrowings
11(758.77)PayablesTrade Payables
12(153.00)PayablesTrade Payables
132,021.74AssetsAssets
14(1,525.00)PayablesTrade Payables
15(218.75)PayablesTrade Payables
16697.70AssetsAssets
172,424.00PayablesOther Receivables
181,965.04PayablesOther Receivables
19(1,260.00)PayablesTrade Payables
201,564.67PayablesOther Receivables
21(315.00)PayablesTrade Payables
221,386.00Related PartiesDue from Related Parties
23262.50Related PartiesDue from Related Parties
24(682.50)Related PartiesDue to Related Parties
25(472.50)Related PartiesDue to Related Parties
26(815.00)Related PartiesDue to Related Parties
27(584.00)Related PartiesDue to Related Parties
281,940.00Related PartiesDue from Related Parties
2975.00Related PartiesDue from Related Parties
301,883.00PayablesOther Receivables
31353.63PayablesOther Receivables
32964.00PayablesOther Receivables
33102.42Cash & BankCash & Bank
34146.00Cash & BankCash & Bank
352,696.57Cash & BankCash & Bank
36(244.20)Trade ReceivablesOther Payables
37622.81Trade ReceivablesTrade Payables
38(69.00)Trade ReceivablesOther Payables
391,260.00Trade ReceivablesTrade Payables
40(1,495.00)Trade ReceivablesOther Payables
41(1,500.00)PayablesTrade Payables
42595.11Trade ReceivablesTrade Payables
43(580.75)PayablesTrade Payables
4475.00Trade ReceivablesTrade Payables
45(269.66)PayablesTrade Payables
46(960.75)PayablesTrade Payables
47(1,342.12)PayablesTrade Payables
48607.15Trade ReceivablesTrade Payables
49488.00Trade ReceivablesTrade Payables
50(110.00)Trade ReceivablesOther Payables
51(446.25)PayablesTrade Payables
52(39.07)Trade ReceivablesOther Payables
53(52.11)Trade ReceivablesOther Payables
54(31.84)Trade ReceivablesOther Payables
55720.00PayablesOther Receivables
56(787.50)PayablesTrade Payables
57(525.00)PayablesTrade Payables
581,017.54Trade ReceivablesTrade Payables
59(667.86)PayablesTrade Payables
601,917.57Trade ReceivablesTrade Payables
61(603.75)PayablesTrade Payables
62(1,178.10)PayablesTrade Payables
63(692.75)PayablesTrade Payables
642,517.00Related PartiesDue from Related Parties
65609.78Trade ReceivablesTrade Payables
66(1,050.00)PayablesTrade Payables
67(259.39)PayablesTrade Payables
68(787.50)PayablesTrade Payables
69807.43PayablesOther Receivables
70717.48PayablesOther Receivables
71823.32PayablesOther Receivables
72(525.00)Trade ReceivablesOther Payables
7395.00Trade ReceivablesTrade Payables
74(315.00)PayablesTrade Payables
75102.83PayablesOther Receivables
76682.80Related PartiesDue from Related Parties
77(455.00)BorrowingsBorrowings
78(598.50)BorrowingsBorrowings
79(577.50)BorrowingsBorrowings
80(120.00)BorrowingsBorrowings
81(488.00)BorrowingsBorrowings
82(1,460.17)BorrowingsBorrowings
83(396.25)BorrowingsBorrowings
84(504.00)BorrowingsBorrowings
85(1,510.00)BorrowingsBorrowings
861,525.00Cash & BankCash & Bank
87(130.00)BorrowingsBorrowings
88(571.57)BorrowingsBorrowings
89(540.75)BorrowingsBorrowings
90775.00Cash & BankCash & Bank
91(766.50)BorrowingsBorrowings
92566.00Cash & BankCash & Bank
931,009.97Cash & BankCash & Bank
941,827.00Cash & BankCash & Bank
95(225.23)BorrowingsBorrowings
96(279.98)BorrowingsBorrowings
97(247.00)BorrowingsBorrowings
98(1,310.00)BorrowingsBorrowings
991,387.62Cash & BankCash & Bank
100128.90Cash & BankCash & Bank
1011,482.52Cash & BankCash & Bank
31.12.2024 (2)
Cell Formulas
RangeFormula
C8:C101C8=IF(B8="Payables",IF(A8<0,"Trade Payables","Other Receivables"),IF(B8="Related parties","Due "&IF(A8<0,"to","from")&" Related Parties",IF(B8="Trade Receivables",IF(A8<0,"Other","Trade")&" Payables",B8)))
Dear Mr. Peter,

This is amazing! My issue has been resolved, and I truly appreciate your help—it’s something I’ve been trying to figure out for a long time. Thank you so much for your great support.

As I’m not very experienced with IT, I’m still unsure about how to paste the data here. My system doesn’t allow me to upload the Excel file. It would be really helpful if you could guide me on how to upload the details, like you did.


Thanks again!


Thanks a lot for your support.
 
Last edited by a moderator:
Upvote 0
This is amazing! My issue has been resolved, and I truly appreciate your help
You're welcome. Thanks for the follow-up.

I have removed part of your post as the rules (see #4) are aimed at keeping all forum-related correspondence public in the forum.

It would be really helpful if you could guide me on how to upload the details, like you did.
I explained in my previous post ..
My signature block at the bottom of this post has help regarding that - see the XL2BB link.
If you follow that link you will get download, installation and use instructions.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top