AW: Abgleich zweier abweichender Tabellen
21.06.2023 17:06:35
UweD
Hallo
ich hätte noch eine VBA Lösung
in ein normales Modul
Option Explicit
Sub Kunden()
Dim Sp1 As Integer, Sp2 As Integer, SpZ As Integer, Z1 As Integer, LR As Long, LRz As Long
Sp1 = 1 'Spalte A
Sp2 = 4 'Spalte D
SpZ = 7 'Zielspalte G
Z1 = 2 'erste Zeile mit Daten
'reset
Columns(SpZ).Resize(, 5).ClearContents
'Spalte 1
LR = Cells(Rows.Count, Sp1).End(xlUp).Row 'letzte Zeile der Spalte
Cells(Z1, Sp1).Resize(LR - Z1 + 1).Copy Cells(Z1, SpZ)
LRz = Cells(Rows.Count, SpZ).End(xlUp).Row + 1
'Spalte 2
LR = Cells(Rows.Count, Sp2).End(xlUp).Row 'letzte Zeile der Spalte
Cells(Z1, Sp2).Resize(LR - Z1 + 1).Copy Cells(LRz, SpZ)
Columns(SpZ).RemoveDuplicates Columns:=1, Header:=xlNo
LRz = Cells(Rows.Count, SpZ).End(xlUp).Row
'Überschriften
Cells(1, SpZ) = "Kunden"
Cells(1, SpZ + 1) = Cells(1, Sp1 + 1)
Cells(1, SpZ + 2) = Cells(1, Sp2 + 1)
Cells(1, SpZ + 3) = "Differenz"
'Formeln
Cells(Z1, SpZ + 1).Resize(LRz - Z1 + 1).FormulaR1C1 = "=SUMIF(C" & Sp1 & ",RC[-1],C" & Sp1 + 1 & ")"
Cells(Z1, SpZ + 2).Resize(LRz - Z1 + 1).FormulaR1C1 = "=SUMIF(C" & Sp2 & ",RC[-2],C" & Sp2 + 1 & ")"
Cells(Z1, SpZ + 3).Resize(LRz - Z1 + 1).FormulaR1C1 = "=RC[-1]-RC[-2]"
End Sub
sieht dann so aus..
| Arbeitsblatt mit dem Namen 'Beispieldatei' |
| | A | B | C | D | E | F | G | H | I | J |
| 1 | Kunden 2022 | Umsatz 2022 | | Kunden 2023 | Umsatz 2023 | | Kunden | Umsatz 2022 | Umsatz 2023 | Differenz |
| 2 | AA1 | 1.300,26 | | A1 | 2.017,89 | | AA1 | 1.300,26 | 16,32 | -1.283,94 |
| 3 | B2 | 2.343,50 | | AA1 | 16,32 | | B2 | 2.343,50 | 5.179,72 | 2.836,22 |
| 4 | C3 | 4.002,56 | | B2 | 5.179,72 | | C3 | 4.002,56 | 0,00 | -4.002,56 |
| 5 | D4 | 312,06 | | BB2 | 1.461,37 | | D4 | 312,06 | 0,00 | -312,06 |
| 6 | E5 | 2.859,72 | | CC3 | 5.895,58 | | E5 | 2.859,72 | 0,00 | -2.859,72 |
| 7 | F6 | 3.189,40 | | DD4 | 6.988,52 | | F6 | 3.189,40 | 0,00 | -3.189,40 |
| 8 | G7 | 4.502,29 | | EE5 | 32,63 | | G7 | 4.502,29 | 3.084,55 | -1.417,74 |
| 9 | H8 | 149,88 | | FF6 | 190,15 | | H8 | 149,88 | 3.018,59 | 2.868,71 |
| 10 | I9 | 657,94 | | G7 | 3.084,55 | | I9 | 657,94 | 2.153,75 | 1.495,80 |
| 11 | J10 | 2.483,35 | | GG7 | 2.534,61 | | J10 | 2.483,35 | 4.797,10 | 2.313,75 |
| 12 | K11 | 1.516,59 | | H8 | 3.018,59 | | K11 | 1.516,59 | 5.405,12 | 3.888,54 |
| 13 | MM13 | 1.833,04 | | HH8 | 4.664,92 | | MM13 | 1.833,04 | 6.147,79 | 4.314,75 |
| 14 | M13 | 1.989,24 | | I9 | 2.153,75 | | M13 | 1.989,24 | 3.998,22 | 2.008,98 |
| 15 | N14 | 1.832,28 | | II9 | 5.005,52 | | N14 | 1.832,28 | 2.388,77 | 556,48 |
| 16 | O15 | 1.348,13 | | J10 | 4.797,10 | | O15 | 1.348,13 | 3.550,57 | 2.202,43 |
| 17 | P16 | 3.893,87 | | JJ10 | 7.308,02 | | P16 | 3.893,87 | 3.812,56 | -81,31 |
| Zelle | Formel |
| H2 | =SUMMEWENN($A:$A;G2;$B:$B) |
| I2 | =SUMMEWENN($D:$D;G2;$E:$E) |
| J2 | =I2-H2 |
...
LG UweD