using macro to check data in table

[lyntan]

hi everyone !
i need help on the codes that can be used for the project im doing now im new to excel and im tasked for this challenging project. i sucks at it so please help

for example :
i have 2 tables. table A and table B
table A stores the years, category ( apple or orange) , the unit price , quantity bought and total price. Table A basically acts as the raw table.

whereas table B acts as a more summarised table that have year, average1, average 2 , average3. firstly i would need to check if table B have that year data from table A.

for example, table A have 2 rows of data

2014, orange, $0.7, 10 quantity , $7.
2014, apple, $0.6, 9 quantity, $5.4

and on button click i would need to check if table B. have 2014 data .

an example of table B will be like :

2014, 0.9 ( unit price of apple/ unit price of orange) , 0.9 ( quantity of apple/quantity of orange) , 0.8 ( total of apple/total of orange)

this will works for all the years im gonna add in the raw data.

so if im going to add another data for 2015. everything remains the same other then the year and the figures in table A.

the button click will have to check if i have 2015 data in table B. if not , i will add in the 2015 data in table B with all the average counted and placed in the corresponding columns in table B.

i hope this is clear ><. please help me. im stuck.
ill greatly appreciate your help.
thank you!

 

[triff]

This code works as per your instruction if there is no data in the sheet "Table 2".

Sub Create_TableB_Array()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim LastRowA As Long
Dim LastRowB As Long
Dim MyARng As Range
Dim MyBRng As Range
Dim MyData(1 To 7) As Variant

Set sh1 = Worksheets("Table A")
Set sh2 = Worksheets("Table B")

LastRowA = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row
LastRowB = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row
Set MyARng = sh1.Range("A2:A" & LastRowA)
Set MyBRng = sh2.Range("A2:A" & LastRowB)
ctr = 0
MyAYear = 0

For Each acel In MyARng
    If acel <> MyAYear Then 'this is a new year
        MyAYear = acel
        
        If WorksheetFunction.CountIf(MyBRng, MyAYear) > 0 Then
            MsgBox "Table B has data for " & MyAYear
        Else
            'There is no data so collect data for line 1
            
            Product = acel.Offset(0, 1)
            MyData(1) = MyAYear
    
            If Product = "Apple" Then
                MyData(2) = acel.Offset(0, 2)
                MyData(3) = acel.Offset(0, 3)
                MyData(4) = acel.Offset(0, 4)
        
            ElseIf Product = "Orange" Then
                MyData(5) = acel.Offset(0, 2)
                MyData(6) = acel.Offset(0, 3)
                MyData(7) = acel.Offset(0, 4)
        
            End If
        
        End If
        
    Else
        'Fill in data from line 2
    
        Product = acel.Offset(0, 1)
        MyData(1) = MyAYear
    
        If Product = "Apple" Then
            MyData(2) = acel.Offset(0, 2)
            MyData(3) = acel.Offset(0, 3)
            MyData(4) = acel.Offset(0, 4)
        
        ElseIf Product = "Orange" Then
            MyData(5) = acel.Offset(0, 2)
            MyData(6) = acel.Offset(0, 3)
            MyData(7) = acel.Offset(0, 4)
        
        End If
                       
        sh2.Cells(LastRowB + 1, 1) = MyData(1)
        sh2.Cells(LastRowB + 1, 2) = MyData(2) / MyData(5)
        sh2.Cells(LastRowB + 1, 3) = MyData(3) / MyData(6)
        sh2.Cells(LastRowB + 1, 4) = MyData(4) / MyData(7)


        LastRowB = LastRowB + 1
        Set MyBRng = sh2.Range("A2:A" & LastRowB)
       ' MsgBox MyBRng.Address
      
    End If
                
Next acel

End Sub

There is an issue which I can't resolve if there is data in sheet "Table B"

I hope this helps

Triff

 

[triff]

This revised version works on my computer.
You need a sheet named "Table A" and one names "Table B"

Sub Create_TableB_Array()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim LastRowA As Long
Dim LastRowB As Long
Dim MyARng As Range
Dim MyBRng As Range
Dim MyData(1 To 7) As Variant

Set sh1 = Worksheets("Table A")
Set sh2 = Worksheets("Table B")

sh2.Columns("C").NumberFormat = "#,##0.000"
sh2.Columns("D").NumberFormat = "#,##0.000"
sh2.Columns("E").NumberFormat = "#,##0.000"

LastRowA = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row
LastRowB = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row
Set MyARng = sh1.Range("A2:A" & LastRowA)
Set MyBRng = sh2.Range("A2:A" & LastRowB)
ctr = 0
MyAYear = 0

For Each acel In MyARng
    If acel <> MyAYear Then 'this is a new year
        MyAYear = acel
        ExistYears = WorksheetFunction.CountIf(MyBRng, MyAYear)
        
        If WorksheetFunction.CountIf(MyBRng, MyAYear) = 0 Then
            'There is no data so collect data for line 1
            
            Product = acel.Offset(0, 1)
            MyData(1) = MyAYear
    
            If Product = "Apple" Then
                MyData(2) = acel.Offset(0, 2)
                MyData(3) = acel.Offset(0, 3)
                MyData(4) = acel.Offset(0, 4)
        
            ElseIf Product = "Orange" Then
                MyData(5) = acel.Offset(0, 2)
                MyData(6) = acel.Offset(0, 3)
                MyData(7) = acel.Offset(0, 4)
        
            End If
            
        Else
            MsgBox "Table B has data for " & MyAYear

        End If
        
    Else
        If WorksheetFunction.CountIf(MyBRng, MyAYear) = 0 Then
        
            'Fill in data from line 2
    
            Product = acel.Offset(0, 1)
            MyData(1) = MyAYear
    
            If Product = "Apple" Then
                MyData(2) = acel.Offset(0, 2)
                MyData(3) = acel.Offset(0, 3)
                MyData(4) = acel.Offset(0, 4)
        
            ElseIf Product = "Orange" Then
                MyData(5) = acel.Offset(0, 2)
                MyData(6) = acel.Offset(0, 3)
                MyData(7) = acel.Offset(0, 4)
        
            End If
                       
            sh2.Cells(LastRowB + 1, 1) = MyData(1)
            sh2.Cells(LastRowB + 1, 2) = MyData(2) / MyData(5)
            sh2.Cells(LastRowB + 1, 3) = MyData(3) / MyData(6)
            sh2.Cells(LastRowB + 1, 4) = MyData(4) / MyData(7)

            LastRowB = LastRowB + 1
            Set MyBRng = sh2.Range("A2:A" & LastRowB)
              
        End If
        
    End If
    
Next acel

End Sub