Summarised data

[BarKerKTM]

Good evening,

My dilemma is as below, I am not having any luck with my (very) basic Excel knowledge, I am hoping someone here can provide the answers I am after.

The goal is to extract data from Table 1 and display in Table 2 which includes counting the number of options available. Table 1 rows can vary and are not a defined number. For the extras I am only interested in Yes, No and Options, but I would like to full count of all extras.

I hope someone can figure out the formula's to use. My sincerest thanks.

 

[RobP]

Morning, if you have used MAcros before, maybe this VBA code can help get you where you need to be : There are lots of places on this forum to get you started with the VBA editor etc. in case of need.

Cheers
Rob

Sub extract_data()

Dim LR, LCR, x, e_yes, e_no, e_option As Long
Dim OptionCode As String

LR = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row 'use Col E to find Last Row of data
LCR = 2 'first row to start results
new_car_row = 2 'first new car row
e_yes = 0
e_no = 0
e_option = 0

'Results appear from column "M". Change if needed to go elsewhere (and Cells(13 > 16 below = col M-->P)
Range("M1:P1") = Range("A1:D1").Value ' put Title headers into Row starting "M1"
Range("Q1") = "Extras Total"
Range("R1") = "Extras Yes"
Range("S1") = "Extras No"
Range("T1") = "Extras Option"
Range("U1:V1") = Range("G1:H1").Value

For x = 2 To LR
  
        OptionCode = Sheet1.Range("F" & x).Value 'count options
        Select Case OptionCode
            Case "Yes"
                e_yes = e_yes + 1
            Case "No"
                e_no = e_no + 1
            Case "Option"
                e_option = e_option + 1
        End Select
        
        If x = new_car_row Then
            Range(Cells(LCR, 13), Cells(LCR, 16)) = Range(Cells(x, 1), Cells(x, 4)).Value 'brand/model/color/fuel
            Range(Cells(LCR, 21), Cells(LCR, 22)) = Range(Cells(x, 7), Cells(x, 8)).Value 'cost/warranty
            
        End If
        
        If Range("A" & x + 1).Value <> "" Then 'if next row not empty, must be a new car next
            
            new_car_row = x + 1
        'print existing totals
            Range("R" & LCR) = e_yes
            Range("S" & LCR) = e_no
            Range("T" & LCR) = e_option
            Range("Q" & LCR) = e_yes + e_no + e_option
            LCR = LCR + 1
            
            'reset option counts
            e_yes = 0
            e_no = 0
            e_option = 0
    
    End If

Next x

'catch for last car summary
Range("R" & LCR) = e_yes
Range("S" & LCR) = e_no
Range("T" & LCR) = e_option
Range("Q" & LCR) = e_yes + e_no + e_option

End Sub

 

[Fuji]

Try
Read the data from Activesheet and output the result in new sheet.

Sub test()
    Dim a, b, t, x, i&, ii&
    With [a1].CurrentRegion
        With .Resize(.Rows.Count + 1)
            x = Filter(.Parent.Evaluate("transpose(if((" & .Columns(1).Address & "<>"""")+(row(1:" & _
                    .Rows.Count & ")=" & .Rows.Count & "),row(1:" & .Rows.Count & ")))"), False, 0)
            If UBound(x) < 2 Then Exit Sub
            a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), Array(1, 2, 3, 4, 6, 6, 6, 6, 7, 8))
            ReDim b(1 To UBound(x), 1 To UBound(a, 2))
        End With
        For i = 1 To UBound(x) - 1
            For ii = 1 To UBound(a, 2)
                If (ii < 5) + (ii > 8) Then
                    b(i, ii) = a(x(i), ii)
                Else
                    b(i, ii) = 0
                End If
            Next
            For ii = x(i) To x(i + 1) - 1
                t = Application.Match(a(ii, 5), Array("yes", "no", "option"), 0)
                If IsNumeric(t) Then b(i, t + 5) = b(i, t + 5) + 1: b(i, 5) = b(i, 5) + 1
            Next
        Next
    End With
    With Sheets.Add.[a1].Resize(, UBound(b, 2))
        .Value = Array("Brand", "Model", "Colour", "Fuel", "Extras Total", "Extras Yes", _
                "Extras No", "Extras Option", "Cost", "Warranty")
        .Rows(2).Resize(UBound(x) - 1) = b
        .EntireColumn.AutoFit
    End With
End Sub

 

[BarKerKTM]

Morning, thanks for your replies. I was hoping to stay away from macros as I am still battling with formulas.

 

[JEC]

Not that hard with office 365 or PowerQuery. In office 2016 you need some more formula's

 

[BarKerKTM]

Any more thoughts?

Regards

 

[RobP]

Do you plan to upgrade to O365 anytime soon, as this could allow perhaps more powerful formulas to be used, as JEC mentioned. Also, is there any other way that you can actually get your data output to be slightly different ? By that, I mean instead of having all the gaps (which are difficult to manage with formula when they are random), can you get them filled with the car / model / fuel etc. in a kind of repeat mode.
There would still be an issue for example if you had 2 silver grey Ford Torinos in your list .. as then there is nothing here to distinguish which is which. And I think therein lies the problem.

This might make using formula afterwards with your version of Excel 2016 a little easier for people here to help you ?

just a thought.

Rob

 

[BarKerKTM]

Hi Rob, thanks for your reply.. The car brands/models are examples only, the actual data in column A is unique cut column B needs to be linked to A.

I was hoping this was a reasonably easy fix but it may not be.. I have now got the counting sorted but I am having with populating column data in Table B, the merged cells make it a challenge.