Help with returning multiple values
Closed
swampy1977
Posts
2
Registration date
Thursday July 30, 2015
Status
Member
Last seen
July 31, 2015
-
Jul 30, 2015 at 06:03 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 3, 2015 at 11:45 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 3, 2015 at 11:45 AM
Related:
- Help with returning multiple values
- Allow multiple downloads chrome - Guide
- How to delete multiple files at once - Guide
- Photoshop multiple selections - Guide
- Which key is used to make multiple line in a single cell - Guide
- If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Excel Forum
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jul 30, 2015 at 11:15 AM
Jul 30, 2015 at 11:15 AM
Hi Swampy,
How about using Filter (found under the Start ribbon)?
Create a header first and then apply the Filter. Then filter on 000, paste the data where you want and then do the same for 004.
If your actual data is more diverse then you sample data then provide a sample of how you want the result to look like.
Best regards,
Trowa
How about using Filter (found under the Start ribbon)?
Create a header first and then apply the Filter. Then filter on 000, paste the data where you want and then do the same for 004.
If your actual data is more diverse then you sample data then provide a sample of how you want the result to look like.
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 3, 2015 at 11:45 AM
Aug 3, 2015 at 11:45 AM
Hi Swampy,
Understood. Since you didn't provide an end result sample, I gave it my own interpretation.
The sheet with all the data is called Database.
The sheet with results is called Result.
What the code does:
The code will copy column A form the Database sheet and transpose it to the first row of the Result sheet.
Then the code will loop through the first row of the Result sheet. Each value will be used to filter the data on the Database sheet and paste back the filtered data (data from column C).
NOTE: For the filter to work the Database sheet needs a header, so create one.
NOTE2: The code expects the Result sheet to be there, so create it.
NOTE3: The sheet names can easily be adjusted by looking at row 5 and 6 of the code.
And here is the code:
Best regards,
Trowa
Understood. Since you didn't provide an end result sample, I gave it my own interpretation.
The sheet with all the data is called Database.
The sheet with results is called Result.
What the code does:
The code will copy column A form the Database sheet and transpose it to the first row of the Result sheet.
Then the code will loop through the first row of the Result sheet. Each value will be used to filter the data on the Database sheet and paste back the filtered data (data from column C).
NOTE: For the filter to work the Database sheet needs a header, so create one.
NOTE2: The code expects the Result sheet to be there, so create it.
NOTE3: The sheet names can easily be adjusted by looking at row 5 and 6 of the code.
And here is the code:
Sub RunMe()
Dim y As Integer
Dim Sh1, Sh2 As Worksheet
Set Sh1 = Sheets("Database")
Set Sh2 = Sheets("Result")
y = 1
Sh1.Select
Range("A2:A" & Range("A1").End(xlDown).Row).Copy
Sh2.Range("A1").PasteSpecial Transpose:=True
Do
Sh1.Range("A1:C1").AutoFilter Field:=2, Criteria1:=Sh2.Cells(1, y).Value
Sh1.Range("C2:C" & Range("C1").End(xlDown).Row).Copy Sh2.Cells(2, y)
y = y + 1
Loop Until Cells(1, y) = vbNullString
Sh1.Range("A1:C1").AutoFilter
End Sub
Best regards,
Trowa
Jul 31, 2015 at 08:08 AM