Help with returning multiple values [Closed]

Report
Posts
2
Registration date
Thursday July 30, 2015
Status
Member
Last seen
July 31, 2015
-
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
-
Hi,

I have 3 columns in Excel. I need to search value from Column A in Column B and return value from Column C.

For example I need to find 004 from Column A in Column B and return all its values from Column C

004 004 340009309
007 000 950000600
014 000 950300302
015 000 950501400
016 004 950701703
017 000 950800604
019 000 950900300
030 000 304000100
031 000 304000704
035 000 304000902
039 000 304001004
041 000 304001103
059 000 304001400
068 000 304002106
078 000 304002502
085 000 304002908
088 000 304003406
094 000 304004201
095 000 304004300
100 000 304004409
102 000 304004508
116 000 304005808
146 000 304006306
152 004 304006405
155 000 304007101
156 000 304007200
157 000 304007507
158 000 304007606
159 000 304007705
160 004 304008005
171 000 304008104
173 000 304008401
175 000 304008500
199 000 304008609
216 000 304008708
235 000 304008807
243 000 304008906
247 000 304009008
248 000 304009206
260 000 304009305
275 000 304009404
281 000 304009602
284 000 304010000
285 000 304010307
303 000 304010703
312 000 304011003
314 000 304011102
364 000 304011201
368 000 304011904
370 000 304012204
371 000 304012303
376 000 304012501
379 000 304012709
402 000 304013207
405 000 304013504
412 000 304013702
417 000 304014200
486 000 304014408
492 000 304014705
500 000 304014804
503 000 304015005
505 000 304015609
569 000 304016404
651 000 304016701
665 000 304017100
686 000 304017209
774 000 304017308
888 000 304017506
897 000 304017803
900 000 304018103
921 000 304018301
945 000 304018400
946 000 304018707
947 000 304018806
957 000 304019403
997 000 304019601
A19 000 304020009
A30 000 304020405
A51 000 304020603
A54 000 304020801
A83 000 304021002
A86 000 304021101
B28 000 304021309
B32 000 304021507

2 replies

Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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
Posts
2
Registration date
Thursday July 30, 2015
Status
Member
Last seen
July 31, 2015

I am afraid that would take too long. I have over 256 values in Column A, Column B is only a small sample. There are over 33k of lines of data in Column B.
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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:
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!