Copy a column from one sheet and paste as row in another using application worksheetfunciton match in vba

Question

I have this excel file in Sheet1:

  A       B          C                       D
Brand   Model      Type           No_of_unit_sold
A       AB123        1                       1
A       AB124        1                       2
A       AB125        1                      11
A       AB113        1                      21
A       AB127        1                      42
A       AB128        1                      12
B       BB123        1                      21
B       BB121        1                      32
AB      BB122        2                      21
AB      BB124        1                      79
AB      BB125        2                      61
AB      BB126        1                      181
B       BB127        1                      28
B       BB128        1                      132
C       CB121        1                      91
C       CB122        1                      73
C       CB123        1                      63
C       CB124        1                      52
C       CB125        1                      85
A       AB129        2                      12
C       CB126        1                      13
C       CB128        1                      94
C       CB129        1                      121

I need the VBA to first scan column "Type" for type 1 only.

and then, to look for brand, copy brand A's name, sum up the number of unit sold and paste it in Sheet2:

 A                     B      C      D      E
Brand                  A      B      AB     C
No_of_unit_sold       89     213    260    592

To sum up the value, I can use sumif function with double criteria. But how do I vary the name of the brand? It's not like using for loop with integer like what I can do for "Type" column...

Also, how do I copy brand name from sheet1 to sheet2 without duplication? Do I use application.worksheetfunction.match? Eg. if the brand name is not found in cells(1,i) of sheet2 then please copy from sheet1 to sheet2?

Is it possible to sort the column with "Brand" alphabetically before any vba is made? This makes it a lot easier if the duplicate brand categories follow each other :) — DirtyDeffy

Nandan A Nandan A · Accepted Answer · 2018-07-12T14:04:27

Below code will works for you. I have used couple of For loops, If statement and Function to achieve this as am not knowledgeable with application.worksheetfunction.match. I tried as below and Worked for me!

Input sheet:

Try the below code.

Sub VBAReader()

Dim CompareBrand As String
'Dim CompareBrands() As Variant
Dim Types, units, Units_sold, ValidatedBrands As Integer
Units_sold = 0
Dim Brand As String
Dim i, j, k, l As Integer
k = 2

Types = Worksheets("Sheet1").Range("C2").End(xlDown).Row

For i = 2 To Types

'Give the type of value
If Worksheets("Sheet1").Range("C" & i).Value = 1 Then
Brand = Worksheets("Sheet1").Range("A" & i).Value

If BrandIsValidated(Brand) = False Then

For j = 2 To Types
If Worksheets("Sheet1").Range("A" & j).Value = Brand And Worksheets("Sheet1").Range("C" & j).Value = 1 Then
units = Worksheets("Sheet1").Range("D" & j).Value
Units_sold = Units_sold + units
End If
Next

Worksheets("Sheet2").Cells(1, k).Value = Brand
Worksheets("Sheet2").Cells(2, k).Value = Units_sold

Units_sold = 0
CompareBrand = Worksheets("Sheet2").Cells(1, k).Value

k = k + 1

End If

End If

Next

End Sub

Function BrandIsValidated(stringToBeFound As String) As Boolean

  ValidatedBrands = Worksheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
   For l = 1 To ValidatedBrands
   If stringToBeFound = Worksheets("Sheet2").Cells(1, (l + 1)).Value Then
   BrandIsValidated = True
   Exit For
   Else
   BrandIsValidated = False

   End If
   Next

End Function

Output sheet:

Note: I am new to VBA so my code wont be friendly. Edits are welcome :)

Copy a column from one sheet and paste as row in another using application worksheetfunciton match in vba

1 Answers