1

Topic: Synchronization of lists of the data

With coming choices, Misters!
However prompt:
We have two lists
The list 1, Spisok2
aa, aa
bb, cc
dd, ee
ee;
ff;
It is necessary to receive the following result:
The list 1, Spisok2
aa, aa
bb;
,cc
dd;
ee, ee
ff;
Dimensionality always different, an array always the two-dimensional

2

Re: Synchronization of lists of the data

And where all this stuff? In Eksele, on sheet, whether that?

3

Re: Synchronization of lists of the data

The task for FULL JOIN is available

4

Re: Synchronization of lists of the data

Akina;
In EXCELe, in it.
While I think as the synchronizer to apply
The general list with removal of doubles.
And concerning it to build two lists.

5

Re: Synchronization of lists of the data

The normal approach. On the third sheet to receive the general list (removal of counterparts in the help), and then to tighten from initial sheets existing records by means of  ().

6

Re: Synchronization of lists of the data

Akina;
I think a macro to adjust since data size always the different.
Formulas to expand not conveniently.

7

Re: Synchronization of lists of the data

Akina;
Whether it is possible to scatter for one pass both lists along the general;
Or two cycles should be applied?

8

Re: Synchronization of lists of the data

What for cycles? They are absolutely not necessary...

9

Re: Synchronization of lists of the data

Akina;

y = 1
k = 0
r = 0
m1 = Worksheets ("List1").Range ("A1:B" and fin1) ' the List 1
m3 = Worksheets ("List2").Range ("A1:B" and fin2) ' the List of coincidence
m4 = Worksheets ("List2").Range ("AA1:AB" and fin2) ' the List not coincidence
m5 = Worksheets ("List3").Range ("A1:B" and fin3) ' the List the general
For i = 1 To fin1
For j = 1 To kon1
If m5 (i, 1) = m1 (j, 1) Then
m3 (i, 1) = m5 (i, 1)
m3 (i, 2) = m5 (i, 2)
k = k + 1
y = True
GoTo loop1
Else
y = False
End If
Next j
If j = kon1 + 1 And y = False Then
t = i
m4 (t, 1) = m5 (i, 1)
m4 (t, 2) = "Not Found"
r = r + 1
End If
y = y + 1
loop1:
Next i

Here something turned out...

10

Re: Synchronization of lists of the data

ThisWorkbook. Sheets (1).Range ("a1:" AND ThisWorkbook. Sheets (1).Range ("a:a").SpecialCells (xlCellTypeLastCell).Address).Copy Destination: = ThisWorkbook. Sheets (3).Range ("a1")
ThisWorkbook. Sheets (2).Range ("a1:" AND ThisWorkbook. Sheets (2).Range ("a:a").SpecialCells (xlCellTypeLastCell).Address).Copy Destination: = ThisWorkbook. Sheets (3).Range ("a:a").SpecialCells (xlCellTypeLastCell).Offset (1, 0)

11

Re: Synchronization of lists of the data

I did not understand couples, how by means of removal of counterparts it is possible to receive the following result .
Here such algorithm leads to the specified result: if cells at the left and on the right do not coincide, to interpose blank cell, with shift downwards to the place of right or left (alternately), further to pass by next line.

Sub Vl ()
Dim i&, p&
i = 2: p = 1
Do While Not IsEmpty (Cells (i, 1)) And Not IsEmpty (Cells (i, 2))
If Cells (i, 1) <> Cells (i, 2) Then
Cells (i, p + 1).Insert xlDown
p = (p + 1) Mod 2
End If
i = i + 1
Loop
End Sub

12

Re: Synchronization of lists of the data

wrote:

I did not understand couples, how by means of removal of counterparts it is possible to receive the following result .

read all. There there is also a second stage. And then still there will be also the third - changeover of formulas by values.

13

Re: Synchronization of lists of the data

wrote:

if cells at the left and on the right do not coincide, to interpose blank cell, with shift downwards to the place of right or left (alternately), further to pass by next line.

it is good, if the list sorted... Or, more truly, steady - in that sense what in this word is put by sorting.