could extract items based on first & last two items and merge values

abdo meghari · Aug 9, 2022

Hi experts
I have two sheets(DATA,REP1) each sheet could be 7800 rows .
every sheet contains items in column B and values into columns C,D should match between two sheets based on column B if the ftwo first items and the two last items are the same thing between two sheets thien should fill the whole items for sheet DATA in columnB in sheet MASTER, and the the whole items for sheet REP1 in column C in sheet MASTER( put each matched item into adjacent cells , and should merge values for column D,E and subtract thae values column D from E as show in column F in sheet MASTER based on adjacent cells in column B,C for each item) . if there is item in sheet DATA and is not existed in sheet REP1, then it considers new item and should highlight by red and show by red in sheet MASTER , also if there is item in sheet REP1 and is not existed in sheet DATA , then it considers new item and should highlight by red and show by red in sheet MASTER
and will change and add data every time in sheets DATA & REP should also updtate automatically by replace data(it meansto clear data in sheet MASTER before bring the data)
let's take example
sheet DATA sheet REP
BS 1200R20 18PR G580 JAP BS 1200R20 G580 JAP
as you see the first two items (BS,1200R20) and the last two items (G580,JAP) are the same thing , then should add in column B,C next to each other of them as in row two and merge the values . last thing if any new item whether in sheet DATA or REP1
contains zero values for two columns C,D together then should n't show in sheet MASTER at all. always when match the item usually contains four or five or six items .....
sometimes can contain three items then can't match based on two first & two last items ,then will be new item directly .

TIRES (1).xlsm

A

B

C

D

1

ITEM

BRAND

QTY

BALANCE

2

1

BS 1200R20 18PR G580 JAP

1,147.00

791.00

3

2

BS 13R22.5 18PR R187 JAP

60.00

-

4

3

BS 315/80R22.5 18PR R184 JAP

98.00

200.00

5

4

BS 315/80R22.5 G580 JAP

20.00

-

6

5

BS 1400R20 VSJ TCF JAP

141.00

-

7

6

BS 1200R24 TCF 18PR G580 JAP

194.00

100.00

8

7

BS 385/65R22.5 R164 JAP

70.00

-

9

8

BS 385/65R22.5 R164 THI

200.00

100.00

10

9

BS 1200R24 22PR G582 JAP

100.00

11

10

BS 265/70R16 D840 THI

40.00

50.00

12

11

BS 205R16C D840 THI

40.00

50.00

13

12

BS 195/65R15 EP150 THI

50.00

-

14

13

BS 205/70R15C R623 THI

200.00

360.00

15

14

BS 215/70R15C R623 THI

100.00

-

16

15

BS 175/70R13 EP150 THI

20.00

-

17

16

BS 235/55R17 T005 THI

40.00

-

18

19

BS 275/70R16 H005 THI

150.00

-

19

20

BS 255/70R15C D840 THI

200.00

-

20

21

BS 1200R20 G580 THI

-

137.00

21

22

BS 315/80R22.5 G582 THI

-

50.00

22

23

BS 175/70R14 EP150 THI

-

50.00

23

24

BS 215/50R17 EP300 THI

-

40.00

24

25

175/65R14 B250 THI

-

150.00

25

26

BS 245/70R16 D684 THI

-

25.00

26

27

BS 225/55R17 EP300 THI

-

20.00

27

28

BS 205/65R15 T005 THI

-

93.00

28

29

BS 205/55R16 T005 THI

-

50.00

29

30

BS 195R15C 623 THI

-

162.00

30

31

BS 255/70R15C D840 THI

-

150.00

31

32

BS 195/55R16 EP300 THI

-

30.00

32

33

BS 195R14C R623 THI

-

16.00

33

34

BS 205/60R16 T005 THI

-

150.00

34

35

BS 255/70R16 H005 THI

-

30.00

35

36

BS 215/45R17 EA03 THI

-

50.00

36

37

BS 1200R20 R187 TCF JAP

-

89.00

37

38

BS 325/95R24 G582 JAP

-

50.00

38

39

FS 215/60R16 99V XL IT

-

329.00

39

40

BS 700R16 12PR R230 JAP

-

40

41

BS 215/70R15C R623 THI

-

41

42

BS 1400R20VSJ TCF JAP

-

42

43

BS 1400R20 TCF R180 JAP

-

43

44

BS 1400R20 TCF R180BZ JAP

-

44

45

BS 185/65R15 B250 JAP

-

45

46

BS 195R15C 613V JAP

-

46

47

BS 205/65R16 EP300 IND

-

47

48

BS 205/70R15C R623 THI

-

48

49

BS 215/55R16 EP300 IND

-

49

50

BS 215/55R17 T005 JAP

-

50

51

BS 215/70R16 D697 IND

-

51

52

BS 225/45R17 050A JAP

-

52

53

BS 225/50R17 EP300 THI

-

53

54

BS 225/55R16 EP300 IND

-

54

55

BS 225/60R16 EP300 IND

-

55

56

BS 225/70R15C R623 JAP

-

56

57

BS 235/45R17 EA03 THI

-

57

58

BS 235/65R17 D697 IND

-

58

59

BS 235/85R16 D697 IND

-

59

60

BS 245/40R18PR 050A JAP

-

60

61

BS 245/65R17 D689 JAP

-

61

62

BS 245/70R17 684A JAP

-

62

63

BS 255/70R16 H005 THI

-

63

64

BS 255/70R15C D840 THI

-

64

65

BS 265/65R17 D840 JAP

-

65

66

BS265/70R15 D697 IND

-

66

67

BS 275/65R18 AL01 JAP

-

67

68

BS 425/65R22.5 R164 JAP

-

68

69

BS 445/65R22.5 R164 JAP

-

69

70

BS 750R16 R230 TCF JAP

-

70

71

BS 225/55R17 EP300 VIT

-

71

72

BS 185/70R14 B250 THI

-

72

73

BS 315/80R22.5-18PR G582 THI

-

73

74

BS 185/65R14 EP150 IND

-

74

75

BS 185/70R13 EP150 IND

-

75

76

BS 215/65R15 T005 IND

-

76

77

BS 265/70R15 D697 IND

-

77

78

BS 315/80R22.5 R184 THI

-

78

79

DT 385/65R22.5 DT40 THI

-

79

80

BS 195R14C R660 TR

-

80

81

BS 235/65R16C R660 TR

-

81

82

BS 195R15C R660 TR

-

82

83

BS 205/70R15C R660 TR

-

83

84

BS 215/70R15C R660 TR

11.00

-

DATA

TIRES (1).xlsm

A

B

C

D

1

ITEM

BRAND

QTY

BALANCE

2

1

BS 215/70R15C R623 THI

100.00

-

3

BS 315/80R22.5 R184 JAP

98.00

200.00

4

2

BS 13R22.5 R187 JAP

60.00

-

5

4

BS 205R16C D840 THI

40.00

50.00

6

5

BS 195/65R15 EP150 THI

50.00

-

7

6

BS 205/70R15C R623 THI

200.00

360.00

8

7

BS 385/65R22.5 R164 JAP

70.00

-

9

8

BS 385/65R22.5 18PR TCF R164 THI

62.00

100.00

10

9

BS 1200R24 G582 JAP

100.00

11

10

BS 315/80R22.5 G580 JAP

20.00

-

12

11

BS 1400R20 VSJ TCF JAP

10.00

-

13

12

BS 1200R24 G580 JAP

10.00

14

13

BS 1200R20 G580 JAP

200.00

10.00

15

14

BS 265/70R16 D840 THI

40.00

50.00

16

15

BS 175/70R13 EP150 THI

20.00

-

17

16

BS 235/55R17 T005 THI

40.00

-

18

17

BS 225/70R16 H005 THI

150.00

-

19

18

BS 255/70R16 H005 THI

95.00

-

20

19

BS 275/70R16 H005 THI

150.00

-

21

20

BS 255/70R15C D840 THI

200.00

-

22

20

BS 315/80R22.5 G582 THI

-

50.00

23

20

BS 175/70R14 EP150 THI

-

50.00

24

20

BS 215/50R17 EP300 THI

-

40.00

25

20

175/65R14 B250 THI

-

150.00

26

20

BS 245/70R16 D684 THI

-

25.00

27

20

BS 225/55R17 EP300 THI

-

20.00

28

20

BS 205/65R15 T005 THI

-

93.00

29

20

BS 205/55R16 T005 THI

-

50.00

30

20

BS 195R15C R623 THI

-

162.00

31

20

BS 255/70R15C D840 THI

-

150.00

32

20

BS 195/55R16 EP300 THI

-

30.00

33

20

BS 195R14C R623 THI

-

16.00

34

20

BS 205/60R16 T005 THI

-

150.00

35

20

BS 255/70R16 H005 THI

-

30.00

36

20

BS 215/45 R17 EA03 THI

-

50.00

37

20

BS 1200R20 R187 TCF JAP

-

89.00

38

20

BS 325/95R24 G582 JAP

-

50.00

39

20

FS 215/60R16 99V XL IT

-

329.00

40

20

BS 700R16 12PR R230 JAP

-

41

20

BS 215/70R15C R623 THI

-

42

20

BS 1400R20VSJ TCF JAP

-

43

20

BS 1400R20 TCF R180 JAP

-

44

20

BS 1400R20 TCF R180BZ JAP

-

45

20

BS 185/65R15 B250 JAP

-

46

20

BS 195R15C 613V JAP

-

47

20

BS 205/65R16 EP300 IND

-

48

20

BS 205/70R15C R623 THI

-

49

20

BS 215/55R16 EP300 IND

-

50

20

BS 215/55R17 T005 JAP

-

51

20

BS 215/70R16 D697 IND

-

52

20

BS 225/45R17 050A JAP

-

53

20

BS 225/50R17 EP300 THI

-

54

20

BS 225/70R15C R623 JAP

-

55

20

BS 235/85R16 D697 IND

-

56

20

BS 245/40R18PR 050A JAP

-

57

20

BS 245/65R17 D689 JAP

-

58

20

BS 245/70R17 684A JAP

-

59

20

BS 255/70R16 H005 THI

-

60

20

BS 255/70R15C D840 THI

-

61

20

BS 265/65R17 D840 JAP

-

62

20

BS265/70R15 D697 IND

-

63

20

BS 275/65R18 AL01 JAP

-

64

20

BS 425/65R22.5 R164 JAP

-

65

20

BS 445/65R22.5 R164 JAP

-

66

20

BS 750R16 R230 TCF JAP

-

67

20

BS 1200R20 18PR G580 THI

-

68

20

BS 225/55R17 EP300 VIT

-

69

20

BS 185/70R14 B250 THI

-

70

20

BS 315/80R22.5-18PR G582 TCF THI

-

71

20

BS 185/65R14 EP150 IND

-

72

20

BS 185/70R13 EP150 IND

-

73

20

BS 215/65R15 T005 IND

-

74

20

BS 265/70R15 D697 IND

-

75

20

BS 315/80R22.5 R184 THI

-

76

20

DT 385/65R22.5 DT40 18PR THI

-

77

20

BS 195R14C R660 TR

-

78

20

BS 235/65R16C R660 TR

-

79

20

BS 235/65R16C R660 IT

11.00

-

80

20

BS 195R15C R660 TR

-

81

20

BS 205/70R15C R660 TR

-

82

20

BS 215/70R15C R660 IT

-

11.00

REP1

result

TIRES (1).xlsm

A

B

C

D

E

F

1

ITEM

LIST1

LIST2

INPUT

OUTPUT

BALANCE

2

1

BS 1200R20 18PR G580 JAP

BS 1200R20 G580 JAP

1,347.00

801.00

546.00

3

2

BS 13R22.5 18PR R187 JAP

BS 13R22.5 R187 JAP

120.00

0.00

120.00

4

3

BS 315/80R22.5 18PR R184 JAP

BS 315/80R22.5 R184 JAP

196.00

400.00

-204.00

5

4

BS 315/80R22.5 G580 JAP

40.00

20.00

6

5

BS 1400R20VSJ TCF JAP

BS 1400R20 VSJ TCF JAP

10.00

0.00

10.00

7

6

BS 1200R24 TCF 18PR G580 JAP

BS 1200R24 G580 JAP

204.00

110.00

94.00

8

7

BS 385/65R22.5 R164 JAP

140.00

0.00

140.00

9

8

BS 385/65R22.5 R164 THI

BS 385/65R22.5 18PR TCF R164 THI

362.00

200.00

162.00

10

9

BS 1200R24 22PR G582 JAP

BS 1200R24 G582 JAP

200.00

0.00

11

10

BS 215/70R15C R660 TR

-

11.00

0.00

11.00

12

11

-

BS 215/70R15C R660 IT

0.00

11.00

-11.00

MASTER

Cell Formulas
Range	Formula
F2:F12	F2	=D2-E2

I hope to covered all of detailes without any confusing .
thanks

abdo meghari · Aug 14, 2022

bebo021999 · Aug 15, 2022

What to do with brand like this (only 3 spaces):
"175/65R14 B250 THI"
in DATA cell B24 and in REP1 cell B25?
Is it actually to be, or does it needed to change?

bebo021999 · Aug 15, 2022

Just give it a first shot:

VBA Code:

Option Explicit
Sub test()
Dim lr&, i&, j&, k&, t&, u1&, pos1&, pos2&, rng, dic As Object, key, st
Dim arr(), arr2(), arr3(), res(), res1(), res2()
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("DATA")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    rng = .Range("B2:D" & lr).Value2
End With
    For i = 1 To lr - 1
        If rng(i, 2) + rng(i, 3) > 0 Then
            If Not dic.exists(Trim(rng(i, 1))) Then
                dic.Add Trim(rng(i, 1)), rng(i, 2) & "|" & rng(i, 3)
            Else
                st = Split(dic(Trim(rng(i, 1))), "|")
                st(0) = st(0) + rng(i, 2): st(1) = st(1) + rng(i, 3)
                dic(Trim(rng(i, 1))) = st(0) & "|" & st(1)
            End If
        End If
    Next
    ReDim arr(1 To dic.Count, 1 To 3)
    For Each key In dic.keys
        k = k + 1
        arr(k, 1) = key: arr(k, 2) = Split(dic(key), "|")(0): arr(k, 3) = Split(dic(key), "|")(1)
    Next
    u1 = dic.Count
    dic.RemoveAll
With Sheets("REP1")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    rng = .Range("B2:D" & lr).Value2
End With
    For i = 1 To lr - 1
        If rng(i, 2) + rng(i, 3) > 0 Then
            If Not dic.exists(Trim(rng(i, 1))) Then
                dic.Add Trim(rng(i, 1)), rng(i, 2) & "|" & rng(i, 3)
            Else
                st = Split(dic(Trim(rng(i, 1))), "|")
                st(0) = st(0) + rng(i, 2): st(1) = st(1) + rng(i, 3)
                dic(Trim(rng(i, 1))) = st(0) & "|" & st(1)
            End If
        End If
    Next
    k = 0
    ReDim arr2(1 To dic.Count, 1 To 5)
    For Each key In dic.keys
        k = k + 1
        pos1 = InStr(InStr(1, key, " ") + 1, key, " ") - 1: pos2 = Len(key) - InStrRev(key, " ", InStrRev(key, " ") - 1)
        arr2(k, 1) = Left(key, pos1): arr2(k, 2) = Right(key, pos2)
        arr2(k, 3) = Split(dic(key), "|")(0): arr2(k, 4) = Split(dic(key), "|")(1): arr2(k, 5) = "x"
    Next
    k = 0: ReDim arr3(1 To u1, 1 To 6)
    For i = 1 To u1
        k = k + 1
        arr3(k, 1) = k: arr3(k, 2) = arr(i, 1): arr3(k, 4) = CLng(arr(i, 2)): arr3(k, 5) = CLng(arr(i, 3))
        arr3(k, 6) = arr3(k, 4) - arr3(k, 5)
        For j = 1 To UBound(arr2)
            If arr(i, 1) Like arr2(j, 1) & "*" & arr2(j, 2) Then
                arr2(j, 5) = ""
                arr3(k, 3) = arr2(j, 1) & " " & arr2(j, 2): arr3(k, 4) = CLng(arr(i, 2)) + CLng(arr2(j, 3))
                arr3(k, 5) = CLng(arr(i, 3)) + CLng(arr2(j, 4)): arr3(k, 6) = arr3(k, 4) - arr3(k, 5)
            End If
        Next
    Next
    ReDim res(1 To u1 + dic.Count, 1 To 6)
    ReDim res1(1 To u1, 1 To 6)
    ReDim res2(1 To dic.Count, 1 To 6)
    j = 0: k = 0
    For i = 1 To UBound(arr3)
        If arr3(i, 3) = "" Then
            j = j + 1
            For k = 1 To 6
                res1(j, k) = arr3(i, k)
            Next
        Else
            t = t + 1
            For k = 1 To 6
                res(t, k) = arr3(i, k)
            Next
        End If
    Next
    For i = 1 To UBound(arr2)
        If arr2(i, 5) = "x" Then
            t = t + 1
            res(t, 3) = arr2(i, 1) & " " & arr2(i, 2)
            res(t, 4) = arr2(i, 3): res(t, 5) = arr2(i, 4)
            res(t, 6) = res(t, 4) - res(t, 5)
        End If
    Next
    For i = 1 To UBound(res1)
        For k = 2 To 6
            res(t + i, k) = res1(i, k)
        Next
    Next
With Sheets("MASTER")
    .Range("A2").Resize(t + i, 6).Value = res
    .Range("A2").CurrentRegion.Borders.LineStyle = xlContinuous
End With
End Sub

abdo meghari · Aug 15, 2022

thanks for following my thread .

Is it actually to be, or does it needed to change?

actually this case should not be at all ,but I give you this case somtimes happen error when enter data , then should be a new item (you can't match based on first and last two items , then should ignore match and it will be new items, is it possible? if not I will correct as the others .
I tested and gives mismatch in this line

VBA Code:

If rng(i, 2) + rng(i, 3) > 0 Then

bebo021999 · Aug 15, 2022

try:

VBA Code:

If Clng(rng(i, 2)) + Clng(rng(i, 3)) > 0 Then

abdo meghari · Aug 16, 2022

unfortunately still the error shows .

bebo021999 · Aug 16, 2022

abdo meghari said:
unfortunately still the error shows .

Which "i" that row get error? (in debug mode, point to the "i" to see instant value of current "i")

abdo meghari · Aug 16, 2022

see the picture please

bebo021999 · Aug 16, 2022

In debug mode, you can point to the letter "i" in line "For i=1 to lr-1" to see instant value of "i"
Also point to rng(i,2) to see instant value of rng(i,2)
We need to detect wich row of array "rng" get error.

could extract items based on first & last two items and merge values

abdo meghari

Well-known Member

Excel Facts

abdo meghari

Well-known Member

bebo021999

Well-known Member

bebo021999

Well-known Member

abdo meghari

Well-known Member

bebo021999

Well-known Member

abdo meghari

Well-known Member

bebo021999

Well-known Member

abdo meghari

Well-known Member

bebo021999

Well-known Member

Similar threads

Forum statistics

Share this page

could extract items based on first & last two items and merge values

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock