Filter Result through excel formula

[Vishaal]

HI, we have the following data where we have many entry in Sheet1 column A, here we have updated some demo data but we have many entries

Product Code
&TS4560&
&TS6484&
&TS2468&
&TS4560& (1)
&TS4560& (2)
&TS4560& (3)
&TS8470&
&TS8474&
&TS6484& (1)
&TS6484& (2)

Want this type of result in sheet 2, where if we have sub code then it will come in column D as shown in Sheet2

Product Code			Sub Code
&TS4560&			&TS4560& (1)
			&TS4560& (2)
			&TS4560& (3)
&TS6484&
&TS2468&
&TS8445&			&TS8445& (1)
			&TS8445& (2)
&TS8474&

we required formula, if it is not possible with formula then vba please

 

[DanteAmor]

Hi @Vishaal

This data in sheet1 does not exist, I guess it is a typo:

But continuing with the criteria, according to the data on sheet1 the result should be:

Dante Amor
	A	B	C	D
1	Product Code			Sub Code
2	&TS4560&			&TS4560& (1)
3				&TS4560& (2)
4				&TS4560& (3)
5	&TS6484&			&TS6484& (1)
6				&TS6484& (2)
7	&TS2468&
8	&TS8470&
9	&TS8474&
Sheet2

I propose the following solution with a macro:

Sub codes()
  Dim a As Variant, b As Variant, ky As Variant, itm As Variant
  Dim dic As Object
  Dim i As Long, k As Long
  Dim s As String
 
  a = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3))
  ReDim b(1 To UBound(a, 1), 1 To 4)
  Set dic = CreateObject("Scripting.Dictionary")
 
  For i = 1 To UBound(a)
    If InStr(1, a(i, 1), "(") = 0 Then s = a(i, 1) Else s = Trim(Split(a(i, 1), "(")(0))
    dic(s) = dic(s) & "|" & a(i, 1)
  Next
 
  For Each ky In dic.keys
    k = k + 1
    b(k, 1) = ky
    itm = Split(dic(ky), "|")
    For i = 2 To UBound(itm)
      b(k, 4) = itm(i)
      k = k + 1
    Next
    If UBound(itm) > 1 Then k = k - 1
  Next
  Sheets("Sheet2").Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Vishaal]

Thanks DanteAmor for helping me,​