From filtered data, if a value is duplicated in the reference values, copy the corresponding filtered data considering duplicates

FariAb

New Member
Joined
Jul 20, 2022
Messages
13
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
I have a code that filters the values from a worksheet (let's name it filter worksheet) depending on the values in a list (in the result worksheet), this list is variable and sometimes the values in it are the same/duplicated, for example:

1664573879546.png

So in the filter worksheet, it filters values 5 and 6 and pastes them on the result worksheet. But since the value "5" is duplicated, I'd like for the filtered value to be duplicated as well. Does anybody know if it could be done via vba or maybe via an excel formula?

Here is the code section of the filtered data:

VBA Code:
With ActiveSheet
With Range("A23:AG115")
.AutoFilter Field:=1, Operator:=xlFilterValues, _
Criteria1:=Split(Join(Application.Transpose(wsResult.Range("L2:L8")), ","), ",")
        End With
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("O24:O" & LR).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
End With
wsResult.Activate
wsResult.Range("M3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False



Thank you in advance!
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,448
Office Version
  1. 365
Platform
  1. Windows
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time. 😊

Instead of vba, could you use a formula approach using this sort of concept?

22 10 01.xlsm
ABCDEFGHIJ
23Hdr1Hdr2Hdr3ListIdxHdr1Hdr2Hdr3
2491a5252b
2552b6565e
2623c5757g
2784d2323c
2865e
2946f
3057g
3158h
3249i
33610j
34
FariAb
Cell Formulas
RangeFormula
H24:J27H24=INDEX(A24:C33,F24:F27,SEQUENCE(,COLUMNS(A24:C33)))
F24:F27F24=AGGREGATE(15,6,ROW(A$24:A$33)/(E24=A$24:A$33),COUNTIF(E$24:E24,E24))-ROW(A$23)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,186,290
Messages
5,957,035
Members
438,285
Latest member
bromssel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top