copy & past if the cell contain symbol or values in specific column

[abdelfattah]

hello
i search way to copy and past in column b when match the data from e2:q with column a if the values matces then copy an paste in col b as my picture

اs(1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	VALU1	VALUE2			VAL	VAL	VAL	VAL	VAL	VAL	VAL	VAL	VAL	VAL	VAL	VAL	VAL
2	OOO	OOO			123	12	34	677	II	UU	OOO	KL	VV	XX	12	AS	111
3	KJH_12	KJH_12			ZXC	123ASD	3456	RT45	KJH_12	19	21	23	25	28	31	34	37
4	4				9	11	13	15	17	19	21	23	25	28	31	34	37
5	34	34			AS	SS	YU	87YU	III	SD7	AS/	SSS	ZXCD	SDDE	FGG	34	37
6	25	25			NB	XZ	AQ	12344	5RTT	19	21	23	25	28	31	34	37
7	7				9	11	13	15	17	19	21	23	25	28	31	34	37
8					9	11	13	15	17	VB	21	VBN	NNN	*SDD	31	ASD	37
1

 

[DanteAmor]

Try with this formula:

Libro1
	A	B	C	D	E	F	G	H	I	J	K
1
2	000	000									000
3	KJH_12	KJH_12							XD
4											000
5
6
7
8
9							KJH_12
Hoja1

Cell Formulas
Range	Formula
B2:B3	B2	=IF(SUMPRODUCT((E2:Q9=A2)*(1)),A2,"")

 

[abdelfattah]

nice formula thanks dante if is possible by vba actually this a simple sample but my data at least 1000 rows as you know if i pull this formula it takes more time
thanks again

 

[DanteAmor]

Try this:

Sub Search_Symbol()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, k As Long, lr As Long, lc As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(3)).Value2
  ReDim c(1 To UBound(a, 1), 1 To 1)
  lr = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
  lc = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
  b = Range(Cells(2, 5), Cells(lr, lc)).Value2
  
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(b, 1)
      For k = 1 To UBound(b, 2)
        If LCase(b(j, k)) = LCase(a(i, 1)) Then
          c(i, 1) = "'" & a(i, 1)
        End If
      Next
    Next
  Next
  
  Range("B2").Resize(UBound(c, 1)).Value = c
End Sub

 

[abdelfattah]

great code ! dante if is possible i would amending the code when write in a2 the value with only match next row as your picture in post #2 if 000 not existed c2:k2 then should empty in b2 and ignored with rests of rows and so on the rests data b3,b4...etc
thanks again

 

[DanteAmor]

Try this:

Sub Macro1()
  Application.ScreenUpdating = False
  With Range("B2:B" & Range("A" & Rows.Count).End(3).Row)
    .FormulaR1C1 = "=IFERROR(HLOOKUP(RC[-1],RC[1]:RC[9],1,0),"""")"
    .Copy
    .PasteSpecial xlPasteValues
  End With
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

 

[abdelfattah]

astonishing work thanks for your formula and codes they're work excellently
best regards

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.