Intercompany reconciliation

[ioanaa]

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

Rows	Entity 1	Entity 2
1	AB	CD
2	EF	GH
3	AB	EF
4	GH	EF
5	EF	CD

<tbody>
</tbody>

 

[SupremeDr]

I don't quite understand what your question is. Can you provide more information on what you need?

 

[ioanaa]

Hi

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

Entity 1 - OLD	Entity 2 -OLD	ENTITY 1- NEW	ENTITY 2 - NEW
AB	AB	AB	CD
AB	CD	CD	AB
AB	EF	AB	EF
AB	GH	EF	AB
CD	AB	AB	GH
CD	CD	GH	AB
CD	EF	CD	EF
CD	GH	EF	CD
EF	AB	CD	GH
EF	CD	GH	CD
EF	EF	EF	GH
EF	GH	GH	EF
GH	AB	AB	AB
GH	CD	CD	CD
GH	EF	EF	EF
GH	GH	GH	GH

<tbody>
</tbody>

​

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

 

[ioanaa]

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

Entity 1	Entity 2	Entity 1 - NEW	Entity 2 - NEW
AB	AB	AB	CD
AB	CD	CD	AB
AB	EF	AB	EF
CD	AB	EF	AB
CD	CD	CD	EF
CD	EF	EF	CD
EF	AB	AB	AB
EF	CD	CD	CD
EF	EF	EF	EF

<tbody>
</tbody>

 

[Gerald Higgins]

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 ?

 

[ioanaa]

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

 

[Akuini]

Try this:

[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

	A​	B​	C​
1​	Entity 1​	Entity 2​	Temp​
2​	AB​	AB​	​
3​	AB​	CD​	​
4​	AB​	EF​	​
5​	CD​	AB​	​
6​	CD​	CD​	​
7​	CD​	EF​	​
8​	EF​	AB​	​
9​	EF​	CD​	​
10​	EF​	EF​	​
11​	AB​	AB​
12​	AB​	CD​
13​	AB​	EF​
14​	CD​	AB​
15​	CD​	CD​
16​	CD​	EF​
17​	EF​	AB​
18​	EF​	CD​
19​	EF​	EF​

Sheet: Sheet4

AFTER (I manually sort by col C)

Excel 2013 32 bit

	A​	B​	C​
1​	Entity 1​	Entity 2​	Temp​
2​	AB​	CD​	AB|CD - 1​
3​	AB​	CD​	AB|CD - 1​
4​	CD​	AB​	AB|CD - 2​
5​	CD​	AB​	AB|CD - 2​
6​	AB​	EF​	AB|EF - 1​
7​	AB​	EF​	AB|EF - 1​
8​	EF​	AB​	AB|EF - 2​
9​	EF​	AB​	AB|EF - 2​
10​	CD​	EF​	CD|EF - 1​
11​	CD​	EF​	CD|EF - 1​
12​	EF​	CD​	CD|EF - 2​
13​	EF​	CD​	CD|EF - 2​
14​	AB​	AB​	zz​
15​	CD​	CD​	zz​
16​	EF​	EF​	zz​
17​	AB​	AB​	zz​
18​	CD​	CD​	zz​
19​	EF​	EF​	zz​

Sheet: Sheet4