Kindly assist with a macro that will compare data on worksheet 1 and consolidate the information on worksheet 2.

Please find attached sample data.

Kindly view the explanation below.

The first sheet contains raw data ; with 4 Columns

The first column (Shop Number)

The second column (Brand Name)

The third column (Type)- different types of brand items

The fourth column (Amount)- amount sold per item type.

The second sheet is expected to consolidate the first sheet with the following columns

Shop Number Brand name Count Amount

The first column will contain a unique representation of the shop number for each brand name.

Count will contain the number of occurrence of the brand name as seen in first sheet (raw data)

Amount will compute the calculation of the values representation of the data as seen in the raw sheet.

Any assistance will be highly appreciated.