Automated deletion of identified information

[Mllam]

Good morning Everyone,

I have been asked of what seems like a simple task but is complex when talking about possibly 100k+ instances.

The issue: The data currently has mistakes and the mistakes have been identified and could be seen here -

data clean.xlsx
	A	B
1	Name	Skus
2	ABA	34X-T23
3	ABA	34X-T24
4	ABA	34X-T25
5	ABA	34X-T26
6	ZEC	AM3-567
7	ZEC	AM3-568
8	BAR	BAR-ME3
9	BAR	BAR-ME4
10	BAR	BAR-ME5
11	BAR	BAR-ME6
12	BAR	BAR-ME7
Sheet1

The existing data where the mistakes are:

data clean.xlsx
	A	B	C	D	E	F	G	H	I	J	K
15	Original Data
16	333-XTR	ABA	34X-T23	HUN	HUN-R32	ZEC	AM3-567
17	332-XT1	BAR	BAR-ME3	BAR	BAR-ME4
18	397-HN2	HUN	HUN-432	ABA	34X-T24	ZEC	AM3-568	BAR	BAR-ME5
19	XTR-345	ABA	34X-T25	BAR	BAR-ME6	HUN	HUN-TE5	BAR	BAR-ME7	ABA	34X-T26
Sheet1

The expected results:

data clean.xlsx
	A	B	C
22	Expected Result
23	333-XTR	HUN	HUN-R32
24	332-XT1
25	397-HN2	HUN	HUN-432
26	XTR-345	HUN	HUN-TE5
Sheet1

Please let me know if there's a formula to complete this task or if I should post this issue in an alternate location on this forum.

Thank you in advance and regards,

Marco

 

[Mllam]

Bump. Anyone have a suggestion? Or a different way of viewing this?

 

[bebo021999]

sumeven.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Skus
2	ABA	34X-T23
3	ABA	34X-T24
4	ABA	34X-T25
5	ABA	34X-T26
6	ZEC	AM3-567
7	ZEC	AM3-568
8	BAR	BAR-ME3
9	BAR	BAR-ME4
10	BAR	BAR-ME5
11	BAR	BAR-ME6
12	BAR	BAR-ME7
13
14
15	Original Data
16	333-XTR	ABA	34X-T23	HUN	HUN-R32	ZEC	AM3-567
17	332-XT1	BAR	BAR-ME3	BAR	BAR-ME4
18	397-HN2	HUN	HUN-432	ABA	34X-T24	ZEC	AM3-568	BAR	BAR-ME5
19	XTR-345	ABA	34X-T25	BAR	BAR-ME6	HUN	HUN-TE5	BAR	BAR-ME7	ABA	34X-T26
20
21
22	Expected Result
23	333-XTR	HUN	HUN-R32
24	332-XT1
25	397-HN2	HUN	HUN-432
26	XTR-345	HUN	HUN-TE5
Sheet2

Cell Formulas
Range	Formula
B23:K26	B23	=IFERROR(IF(INDEX(16:16,AGGREGATE(15,6,COLUMN($B16:$K16)/(COUNTIF($A$2:$B$12,$B16:$K16)=0),COLUMNS($A:A)))=0,"",INDEX(16:16,AGGREGATE(15,6,COLUMN($B16:$K16)/(COUNTIF($A$2:$B$12,$B16:$K16)=0),COLUMNS($A:A)))),"")

 

[Peter_SSs]

possibly 100k+ instances.

I suspect that a formula approach with that much data might be a bit cumbersome.

Here is a possible macro approach. It would need some modification once we knew how to decide the extent of the data. Currently I have coded it to the ranges shown in your sample data to see if the concept is correct.

Sub Mllam()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A1").CurrentRegion.Value
  For i = 2 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = 1
  Next i
  a = Range("A16").CurrentRegion.Value
  uba2 = UBound(a, 2)
  ReDim b(1 To UBound(a), 1 To uba2)
  b(1, 1) = "Result"
  For i = 2 To UBound(a)
    b(i, 1) = a(i, 1)
    k = 0
    For j = 2 To uba2 Step 2
      If IsEmpty(a(i, j)) Then Exit For
      If Not d.exists(a(i, j) & "|" & a(i, j + 1)) Then
        k = k + 2
        b(i, k) = a(i, j): b(i, k + 1) = a(i, j + 1)
      End If
    Next j
  Next i
  Range("A" & Rows.Count).End(xlUp).Offset(3).Resize(UBound(b), uba2).Value = b
End Sub

My sample data in rows 1:19, macro results in rows 22+

Mllam.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Skus
2	ABA	34X-T23
3	ABA	34X-T24
4	ABA	34X-T25
5	ABA	34X-T26
6	ZEC	AM3-567
7	ZEC	AM3-568
8	BAR	BAR-ME3
9	BAR	BAR-ME4
10	BAR	BAR-ME5
11	BAR	BAR-ME6
12	BAR	BAR-ME7
13
14
15	Original Data
16	333-XTR	ABA	34X-T23	HUN	HUN-R32	ZEC	AM3-567
17	332-XT1	BAR	BAR-ME3	BAR	BAR-ME4
18	397-HN2	HUN	HUN-432	ABA	34X-T24	ZEC	AM3-568	BAR	BAR-ME5
19	XTR-345	ABA	34X-T25	BAR	BAR-ME6	HUN	HUN-TE5	BAR	BAR-ME7	ABA	34X-T26
20
21
22	Result
23	333-XTR	HUN	HUN-R32
24	332-XT1
25	397-HN2	HUN	HUN-432
26	XTR-345	HUN	HUN-TE5
27
Mistakes