Hello, I have a table that I'm trying to extract from it data and copy to a new table in the same sheet, based on text value. I've attached a sample. Thanks in advance for any help.
Hello, I have a table that I'm trying to extract from it data and copy to a new table in the same sheet, based on text value. I've attached a sample. Thanks in advance for any help.
According to your attachment a beginner starter you must first paste to the Sheet1 worksheet module
then this event raises when you modify the cell I3 just with exactly the same color than in the source range :
PHP Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim L&, R&, J$
If Target.Address = "$I$3" Then
Application.EnableEvents = False
Target.CurrentRegion.Offset(1).Clear
If Target > "" Then
L = 3
For R = 2 To [A1].CurrentRegion.Rows.Count
J = Join(Filter(Evaluate("IF($B$" & R & ":$E$" & R & "=$I$3,$B$1:$E$1)"), False, False), ", ")
If J > "" Then L = L + 1: Rows(L).Columns("H:I") = Array(Cells(R, 1), J)
Next
End If
Application.EnableEvents = True
End If
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 02-25-2020 at 09:43 AM. Reason: optimization …
Check my solution.Please Login or Register to view this content.
Best Regards,
Maras.
This is very easy with PowerQuery in Excel 365.
M Code:
Please Login or Register to view this content.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks