If an Excel criteria is met in one column, record the value of the column next to the matching criteria into a range

Question

Perhaps I just haven't been looking in the right places, but I don't think this should be as difficult as I am finding it to be.

I have a spreadsheet, that CANNOT be sorted (it's in a production line, connected to individual sheets that bring other data in, filled out by many different people). The data that comes in is essentially random, and looks like this:

''    Type    Sieve #40
''
''    Truck       55%
''    Truck       55%
''    CoA         48%
''    Basement    55%
''    Bin2        55%
''    Bin1        55%
''    Hopper      57%
''    Basement    58%
''    Bin2        54%
''    Bin1        58%
''    Hopper      56%
''    Truck       56%
''    CoA         47%
''    Basement    55%
''    Bin2        57%
''    Bin1        61%
''    Hopper      50%

Now, I need a macro that can find the sample type (truck, bin1, etc.) and puts each value that corresponds to that sample type into a range. Then I can plot the ranges on a chart.

For example, the "Truck" range would have the numbers 55%, 55%, 56%.

So there are 6 different sample types which means 6 different ranges, which means 6 different series on my chart.

I have all the code written to plot the chart, and all the code written to gather the data into these two columns. I am just missing this piece.

Ideally, for example, there would be a way to set up a For loop that goes from row 1 to the last row of the sheet, and when it finds "truck", it assigns the number in the column next to "truck" to the first spot in a new array. Then the next instance of "truck" fills the next spot in the "Truck" array, and so on.

Adding sheets is very nearly impossible for me, because the operators must add a new sheet for each sample, which then gets some data pulled into this summary sheet.

Answer 1

edited as per OP's last clarifications

try this

Option Explicit

Sub main()

Dim dataRng As Range, dbRng As Range, helperRng As Range, cell As Range, found As Range
Dim rangeArray() As Range
Dim iRng As Long

With Worksheets("MySheet") '<= change it to your actual worksheet name
    Set dbRng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).Resize(, 2) '<= change "A1" to your actual data first up-left cell address
    Set dataRng = dbRng.Offset(1).Resize(dbRng.Rows.Count - 1) 'extrapolate data only (headers off)
    'Set helperRng = dbRng.Offset(.UsedRange.Rows.Count + 1, .UsedRange.Columns.Count + 1).Cells(1, 1) ' localize "helper" range out of sheet used range
    Set helperRng = .Range("AA1") ' localize "helper" range from cell "AA1" 
End With
dataRng.Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues).Copy Destination:=helperRng ' copy relevant data into "helper" rang

With helperRng
    If .CurrentRegion.Rows.Count > 1 Then .CurrentRegion.RemoveDuplicates Columns:=Array(1), Header:=xlNo ' take only samples unique values
    With .CurrentRegion
        ReDim rangeArray(1 To .Rows.Count) 'size the array to sample unique values number
        For iRng = 1 To .Rows.Count 'loop through sample unique values
            dbRng.AutoFilter field:=1, Criteria1:=helperRng(iRng, 1) ' filter data accordingly to current sample value
            Set rangeArray(iRng) = dataRng.Columns(2).SpecialCells(xlCellTypeVisible) 'store filtered rows columns 2 ranges
            .AutoFilter
        Next iRng
    End With
'    .ClearContents '<== remove the comment once you're done with the example
End With

' here follows an example of exploiting rangeArray array
' once you're done with it, remove this example and uncomment the ".ClearContents" statement by the end of preceeding "With ... End With" block
For iRng = 1 To UBound(rangeArray)
    rangeArray(iRng).Copy
    helperRng.Offset(iRng - 1, 1).Resize(1, rangeArray(iRng).Count).PasteSpecial xlPasteAll, Transpose:=True
Next iRng

End sub

it'll work with whatever samples different values