Forum

Notifications
Clear all

Match Positive and negative values based on multiple criteria

5 Posts
3 Users
0 Reactions
148 Views
(@revathi-reference)
Posts: 3
Active Member
Topic starter
 

Hi Team,

Could you please help us on the alternate formula to match the Positive negative values based on the CoCd(Column A) & Account (Column B) .

attached spread for your reference, where the account and Cocd is same then can use the formula applied in Sheet Individual accounts.

if the all the lines are at consolidated as in sheet Consolidated sheets, the same formula will not be work ,

so the Concat has been used for CoCd, Account and Amount  and applied the formula. could you please help with any alternate formula without using concat. 

 
Posted : 07/09/2022 11:14 am
(@revathi-reference)
Posts: 3
Active Member
Topic starter
 

And please note and help on the formula by considering  the CoCd and Account are in Text format , Not in Numbers .

 
Posted : 07/09/2022 12:31 pm
Md Saliha
(@navsal66)
Posts: 108
Estimable Member
 

Hi Revathi

What i understand i have updated in highlighted

 
Posted : 29/09/2022 5:48 am
Riny van Eekelen
(@riny)
Posts: 1185
Member Moderator
 

Hi,

In D2 on the "Consolidated" sheet, you can use this formula:

=IF(C2="","",A2&B2&"/"&ABS(C2)&"_"&COUNTIF(C$2:C2,C2))

and copy it down.

 

This combines the concatenated values from A and B with the absolute value in C and adds the counter. And now it doesn't matter if the account numbers are numeric or alpha-numeric. Revised your file. See attached.

Riny

 
Posted : 30/09/2022 12:58 am
(@revathi-reference)
Posts: 3
Active Member
Topic starter
 

Thank you Riny, It works. 

 
Posted : 17/11/2022 11:39 am
Share: