Intercompany reconciliation

I

ioanaa

New Member
Joined
Aug 22, 2019
Messages
4
Hi

I have approx 500 companies which could have a balance with each other. So in total are approx 250K combinations.

I am trying to group them to see whether the balances between them are correct or not.

For example AB-CD should be grouped with CD-AB. I have tried to find MATCH and INDEX but it keeps crashing due to the large number of cells.

I have also tried to use LOOP in VBA but is looping too many times and it crashed again. Does anyone have any suggestions?

Thank you

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Rows[/TD]
[TD="align: center"]Entity 1[/TD]
[TD="align: center"]Entity 2[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]CD[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]GH[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]EF[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]GH[/TD]
[TD="align: center"]EF[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]CD[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi

I am trying to group matching entities (i.e. AB -CD with CD-AB) so I can analyse their balances.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Entity 1 - OLD[/TD]
[TD]Entity 2 -OLD[/TD]
[TD="align: center"]ENTITY 1- NEW[/TD]
[TD="align: center"]ENTITY 2 - NEW[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]CD[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]AB[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]EF[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]GH[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]AB[/TD]
[/TR]
[TR]
[TD="align: center"]CD[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]GH[/TD]
[/TR]
[TR]
[TD="align: center"]CD[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]GH[/TD]
[TD="align: center"]AB[/TD]
[/TR]
[TR]
[TD="align: center"]CD[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]EF[/TD]
[/TR]
[TR]
[TD="align: center"]CD[/TD]
[TD="align: center"]GH[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]CD[/TD]
[/TR]
[TR]
[TD="align: center"]EF[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]GH[/TD]
[/TR]
[TR]
[TD="align: center"]EF[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]GH[/TD]
[TD="align: center"]CD[/TD]
[/TR]
[TR]
[TD="align: center"]EF[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]GH[/TD]
[/TR]
[TR]
[TD="align: center"]EF[/TD]
[TD="align: center"]GH[/TD]
[TD="align: center"]GH[/TD]
[TD="align: center"]EF[/TD]
[/TR]
[TR]
[TD="align: center"]GH[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AB[/TD]
[/TR]
[TR]
[TD="align: center"]GH[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]CD[/TD]
[/TR]
[TR]
[TD="align: center"]GH[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]EF[/TD]
[/TR]
[TR]
[TD="align: center"]GH[/TD]
[TD="align: center"]GH[/TD]
[TD="align: center"]GH[/TD]
[TD="align: center"]GH[/TD]
[/TR]
</tbody>[/TABLE]

​
In the first 2 columns is a small extract of the data I have (Entity 1 that has a relationship with Entity 2).

In columns 3+4 is what I am trying to achieve. I want to group them so I can see the net impact on their balances.

If I concatenate I obtain ABCD which I want to group with CDAB.

Please let me know if it makes a bit more sense?

Thank you
 
Upvote 0
Hi

I am trying to group companies based on their relationship. First 2 columns are an extract of my existing data.

I have highlighted in red what I am trying to group together. Columns 3+4 are what I am trying to achieve.

Because there are 250K combinations it takes forever to try to do it manually.

Please let me know if it doesn't make sense

Thank you


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Entity 1[/TD]
[TD="align: center"]Entity 2[/TD]
[TD="align: center"]Entity 1 - NEW[/TD]
[TD="align: center"]Entity 2 - NEW[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]CD[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]AB[/TD]
[/TR]
[TR]
[TD="align: center"]AB[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]EF[/TD]
[/TR]
[TR]
[TD="align: center"]CD[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]AB[/TD]
[/TR]
[TR]
[TD="align: center"]CD[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]EF[/TD]
[/TR]
[TR]
[TD="align: center"]CD[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]CD[/TD]
[/TR]
[TR]
[TD="align: center"]EF[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AB[/TD]
[/TR]
[TR]
[TD="align: center"]EF[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]CD[/TD]
[TD="align: center"]CD[/TD]
[/TR]
[TR]
[TD="align: center"]EF[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]EF[/TD]
[TD="align: center"]EF[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I think I get what you are trying to do, but it's not all that clear.

Perhaps consider, instead of matching AB-CD with CD-AB, perhaps a better approach would be to describe AB-CD as AB-CD.1 and then describe CD-AB as AB-CD.2.

Then comparing AB-CD.1 with AB-CD.2 might be easier.

Just a thought.

Anyway, whatever you decide, if you want to concatenate the 2 codes, go ahead.
Are you having a problem with concatenation ?
 
Upvote 0
I am sorry for the duplicated reply.


My issue is that I have 250,000 rows.

So for example AB owes CD €100 (this is on row 3). In theory CD is expecting a receipt from AB of €100 (this is on row 145,000)

I want to see if the balance between AB and CD is the same with CD and AB. In order to do this I want to group them so I can see them more clear but I am failing at doing that.

I have tried to do AB.CD (for row 3) and then CD.AB (for row 145,000), but I don't know how to sort them.

Companies have between 4 to 7 letters so is not very consistent. I have tried with pivot table but again it did not work so I am a bit stuck.

My other idea was to try to find the correspondent pair and copy and paste it onto a different sheet, but it was looping too many times and crashed.


Sub grouping()
Dim lrow As Long
Dim rng1 As Range


lrow = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Row




For a = 4 To lrow


b = 4

Do Until b > lrow


If Sheets("Summary").Cells(a, 1).Value = Sheets("Summary").Cells(b, 2).Value Then
If Sheets("Summary").Cells(a, 2).Value = Sheets("Summary").Cells(b, 1).Value Then

Sheets("Summary").Range(Cells(a, 1), Cells(a, 11)).Copy Sheets("Group").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Sheets("Summary").Range(Cells(b, 1), Cells(b, 11)).Copy Sheets("Group").Cells(Rows.Count, 1).End(xlUp).Offset(1)

End If
End If


b = b + 1
Loop

Next a


End Sub
 
Upvote 0
Try this:

Code: 
[FONT=lucida console][color=Royalblue]Sub[/color] a1107774a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1107774-vba-excel-clear-entire-col-expect-a1-post5330421.html#post5330421[/color][/i]

[color=Royalblue]Dim[/color] i [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] va, vb
[color=Royalblue]Dim[/color] d [color=Royalblue]As[/color] [color=Royalblue]Object[/color]

[color=Royalblue]Set[/color] d = CreateObject([color=brown]"scripting.dictionary"[/color])
d.CompareMode = vbTextCompare
va = Range([color=brown]"A2:B"[/color] & Cells(Rows.count, [color=brown]"A"[/color]).[color=Royalblue]End[/color](xlUp).Row)
[color=Royalblue]ReDim[/color] vb([color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color]), [color=crimson]1[/color] [color=Royalblue]To[/color] [color=crimson]1[/color])
[color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
    a = va(i, [color=crimson]1[/color]): b = va(i, [color=crimson]2[/color])
    [color=Royalblue]If[/color] a = b [color=Royalblue]Then[/color]
    vb(i, [color=crimson]1[/color]) = [color=brown]"zz"[/color]
    [color=Royalblue]Else[/color]
        [color=Royalblue]If[/color] [color=Royalblue]Not[/color] d.Exists(b & [color=brown]"|"[/color] & a) [color=Royalblue]Then[/color]
        d(a & [color=brown]"|"[/color] & b) = Empty
        vb(i, [color=crimson]1[/color]) = a & [color=brown]"|"[/color] & b & [color=brown]" - 1"[/color]
        [color=Royalblue]Else[/color]
        vb(i, [color=crimson]1[/color]) = b & [color=brown]"|"[/color] & a & [color=brown]" - 2"[/color]
        [color=Royalblue]End[/color] [color=Royalblue]If[/color]
    [color=Royalblue]End[/color] [color=Royalblue]If[/color]
[color=Royalblue]Next[/color]

Application.ScreenUpdating = [color=Royalblue]False[/color]
[i][color=seagreen]'Put the result in col C:[/color][/i]
Range([color=brown]"C2"[/color]).Resize(UBound(vb, [color=crimson]1[/color]), [color=crimson]1[/color]) = vb
Application.ScreenUpdating = [color=Royalblue]True[/color]
[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]


BEFORE

Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
1​
[/td][td=bgcolor:#BDD7EE]
Entity 1​
[/td][td=bgcolor:#BDD7EE]
Entity 2​
[/td][td=bgcolor:#BDD7EE]
Temp​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
2​
[/td][td]
AB​
[/td][td]
AB​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
3​
[/td][td]
AB​
[/td][td]
CD​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
4​
[/td][td]
AB​
[/td][td]
EF​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
5​
[/td][td]
CD​
[/td][td]
AB​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
6​
[/td][td]
CD​
[/td][td]
CD​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
7​
[/td][td]
CD​
[/td][td]
EF​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
8​
[/td][td]
EF​
[/td][td]
AB​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
9​
[/td][td]
EF​
[/td][td]
CD​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
10​
[/td][td]
EF​
[/td][td]
EF​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
11​
[/td][td]
AB​
[/td][td]
AB​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
12​
[/td][td]
AB​
[/td][td]
CD​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
13​
[/td][td]
AB​
[/td][td]
EF​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
14​
[/td][td]
CD​
[/td][td]
AB​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
15​
[/td][td]
CD​
[/td][td]
CD​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
16​
[/td][td]
CD​
[/td][td]
EF​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
17​
[/td][td]
EF​
[/td][td]
AB​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
18​
[/td][td]
EF​
[/td][td]
CD​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
19​
[/td][td]
EF​
[/td][td]
EF​
[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]


AFTER (I manually sort by col C)

Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
1​
[/td][td=bgcolor:#BDD7EE]
Entity 1​
[/td][td=bgcolor:#BDD7EE]
Entity 2​
[/td][td=bgcolor:#BDD7EE]
Temp​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
2​
[/td][td]
AB​
[/td][td]
CD​
[/td][td]
AB|CD - 1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
3​
[/td][td]
AB​
[/td][td]
CD​
[/td][td]
AB|CD - 1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
4​
[/td][td]
CD​
[/td][td]
AB​
[/td][td]
AB|CD - 2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
5​
[/td][td]
CD​
[/td][td]
AB​
[/td][td]
AB|CD - 2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
6​
[/td][td]
AB​
[/td][td]
EF​
[/td][td]
AB|EF - 1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
7​
[/td][td]
AB​
[/td][td]
EF​
[/td][td]
AB|EF - 1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
8​
[/td][td]
EF​
[/td][td]
AB​
[/td][td]
AB|EF - 2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
9​
[/td][td]
EF​
[/td][td]
AB​
[/td][td]
AB|EF - 2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
10​
[/td][td]
CD​
[/td][td]
EF​
[/td][td]
CD|EF - 1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
11​
[/td][td]
CD​
[/td][td]
EF​
[/td][td]
CD|EF - 1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
12​
[/td][td]
EF​
[/td][td]
CD​
[/td][td]
CD|EF - 2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
13​
[/td][td]
EF​
[/td][td]
CD​
[/td][td]
CD|EF - 2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
14​
[/td][td]
AB​
[/td][td]
AB​
[/td][td]
zz​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
15​
[/td][td]
CD​
[/td][td]
CD​
[/td][td]
zz​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
16​
[/td][td]
EF​
[/td][td]
EF​
[/td][td]
zz​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
17​
[/td][td]
AB​
[/td][td]
AB​
[/td][td]
zz​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
18​
[/td][td]
CD​
[/td][td]
CD​
[/td][td]
zz​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
19​
[/td][td]
EF​
[/td][td]
EF​
[/td][td]
zz​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Comparing Data from multiple spread-sheets and fish out wanted data into a separate spread-sheet organized per tab
2 3
Replies
24
Views
2K
Harry_1234
H
H
  • Solved
summarizing data from multiple tabs in Excel
2
Replies
18
Views
2K
Yongle
Yongle
H
  • Question Question
Macro: Convert pdf into Excel and extract certain data to put together with file name
Replies
1
Views
3K
HeinrichPaul
H
H
  • Question Question
excel vba : How to move data from rows to column
Replies
6
Views
1K
Peter_SSs
Peter_SSs
G
  • Question Question
Excel VBA Macro steps through ok but when run it causes duplication
Replies
2
Views
4K
Gassy
G

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

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