Create a report dashboard to compare between two sheets data

[almaita]

Hi,

I have the below drug list shared by insurance companies each Monday, this list always varies with multiple fields such as drugs added/removed, drugs shifted to tier 2 from tier 3 or vise versa, unit price increase.. and all the the fields.

example

February 21 sheet lets call it sheet 1 in the workbook

New Drug Code

Insurance Plan

New Tier

Package Name

Generic Name

Strength

Dosage Form

Package Size

Package Price to Public

Package Price to Pharmacy

Unit Price to Public

Unit Price to Pharmacy

Status

Delete Effective Date

Last Change

Agent Name

Manufacturer Name

J28-6164-06355-01

NCI-Non Coverage Item

3

PROSTOFORTE

(Ayruvedic) Prostoforte

Combination

Capsules

50s

62.5

50.65

1.25

1.01

Active

8 August 2018

NUPAL REMEDIES TRADING

New Udaya Pharmacy & Ayurvedic Laboratories, INDIA

T96-7632-07527-01

NCI-Non Coverage Item

3

MAGNESIUM PHOSPHORICUM 6X- ADLER PHARMA

(Hemeopathy) Magnesium Phosphoricum

250 mg

Tablets

200s

48

36

0.24

0.18

Active

10 November 2019

ULTRAPHARMA DRUG STORE

Adler Pharma Produktion und Vertrieb GmbH, AUSTRIA

D98-2596-00001-01

NCI-Non Coverage Item

3

IRICAR

(Homeopathy)

Combination

Cream

50g

48

36

48

36

Active

8 June 2016

ULTRA MEDICAL STORE (ULTRAMED)

Deutsche Homoopathie Union, GERMANY

February 28 updated sheet, lets call it sheet 2 in the workbook

New Drug Code

Insurance Plan

New Tier

Package Name

Generic Name

Strength

Dosage Form

Package Size

Package Price to Public

Package Price to Pharmacy

Unit Price to Public

Unit Price to Pharmacy

Status

Delete Effective Date

Last Change

Agent Name

Manufacturer Name

J28-6164-06355-01

NCI-Non Coverage Item

2

PROSTOFORTE

(Ayruvedic) Prostoforte

Combination

Capsules

50s

62.5

50.65

1.25

1.01

Active

8 August 2018

NUPAL REMEDIES TRADING

New Udaya Pharmacy & Ayurvedic Laboratories, INDIA

T96-7632-07527-01

NCI-Non Coverage Item

3

MAGNESIUM PHOSPHORICUM 6X- ADLER PHARMA

(Hemeopathy) Magnesium Phosphoricum

250 mg

Tablets

200s

60

36

1

0.18

Active

10 November 2019

ULTRAPHARMA DRUG STORE

Adler Pharma Produktion und Vertrieb GmbH, AUSTRIA

G32-0766-00004-01

NCI-Non Coverage Item

3

CALMVALERA HEVERT

(Homeopathy) (CALMVALERA HEVERT)

Combination

Oral Solution

50ml

72

54

72

54

Active

27 November 2018

ULTRA MEDICAL STORE (ULTRAMED)

Hevert-Arzneimittel GmbH & Co. KG, GERMANY

i need a sheet in the workbook titled lets say "Report" that capture all the differences between both sheets. for example (sheet1 row 3 IRICAR is removed from the update, while "CALMVALERA HEVERT", PROSTOFORTE in sheet 1 was in tier 3, it got downgraded to tier 2 in the updated sheet, the package price to public column jumped for Magnesium from 48 to 60.

how can i show all differences between both sheets, and if that can be solved by VBA or formulas, can i also have the cells highligted in red for the drugs removed, and in green for the druges added, same for the prices increase?

*Note: i need to capture all changes in the sheet and not only prices

 

[EFANYoutube]

Ok almaita,

This did take some time so hope this works for you

'This is a 2 part process
'First to check all the Sheet2 data against Sheet 1 and report all changes and additions
'Then check if there are any removed and add them to the report

Sub Run_Report()
Dim ShOld As Worksheet
Dim ShNew As Worksheet
Dim ShRpt As Worksheet
Dim Drug As Range
Dim DrugCols As Collection
Dim RptRow As Integer
Dim cell As Range
Dim Itm As Integer
Set DrugCols = New Collection
Set ShOld = ThisWorkbook.Sheets("Sheet1")
Set ShNew = ThisWorkbook.Sheets("Sheet2")
Set ShRpt = ThisWorkbook.Sheets("Report")
RptRow = 2
Itm = 1
ShRpt.Cells.Delete
'Write Headers
ShOld.Range("1:1").Copy ShRpt.Range("A1")
For Each Drug In ShNew.Range("A2:A1500").Cells
    If Drug = "" Then Exit For
    'Check for changes and additions
    Set DrugCols = Process_Drug(Drug, ShOld, False)
    'Write changes and additions
    For Each cell In Range(ShRpt.Cells(RptRow, 1), ShRpt.Cells(RptRow, 17)).Cells
        If cell.Value = "" Then
            cell.Value = DrugCols(Itm)(0)
            If DrugCols(Itm)(1) = False Then cell.Interior.Color = vbGreen
            Itm = Itm + 1
        Else
            Exit For
        End If
    Next cell
    Itm = 1
    RptRow = RptRow + 1
Next Drug
For Each Drug In ShOld.Range("A2:A1500").Cells
    If Drug = "" Then Exit For
    'Check for Deletions and turn them red
    Set DrugCols = Process_Drug(Drug, ShNew, True)
    'Write changes and additions
    If DrugCols.Count <> 0 Then
        For Each cell In Range(ShRpt.Cells(RptRow, 1), ShRpt.Cells(RptRow, 17)).Cells
            cell.Value = DrugCols(Itm)(0)
            If DrugCols(Itm)(1) = False Then cell.Interior.Color = vbRed
            Itm = Itm + 1
        Next cell
    RptRow = RptRow + 1
    End If
Next Drug
ShRpt.Columns.AutoFit
End Sub


Function Process_Drug(DrugToCheck As Range, SheetToCheck As Worksheet, FindRemoved As Boolean) As Collection
    'This will take 1 row of a drug and compair it to all drugs in the other sheet
    Dim Drug As Range
    Dim DrugCol As Integer
    Dim Col(1) As Variant
    Set Process_Drug = New Collection
    For Each Drug In SheetToCheck.Range("A2:A1500").Cells
        If Drug = "" Then Exit For
        If Drug = DrugToCheck Then
            If FindRemoved Then Exit Function
            'Check both entries for differences
            For DrugCol = 0 To 16
                If DrugToCheck.Offset(, DrugCol).Value = Drug.Offset(, DrugCol).Value Then
                    Col(0) = DrugToCheck.Offset(, DrugCol).Value
                    Col(1) = True
                Else
                    Col(0) = DrugToCheck.Offset(, DrugCol).Value
                    Col(1) = False
                End If
                Process_Drug.Add Col
                Erase Col()
            Next DrugCol
            Exit Function
        End If
    Next Drug
    If Process_Drug.Count = 0 Then
        For DrugCol = 0 To 16
            Col(0) = DrugToCheck.Offset(, DrugCol).Value
            Col(1) = False
            Process_Drug.Add Col
            Erase Col()
        Next DrugCol
    End If
End Function

almaita.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	New Drug Code	Insurance Plan	New Tier	Package Name	Generic Name	Strength	Dosage Form	Package Size	Package Price to Public	Package Price to Pharmacy	Unit Price to Public	Unit Price to Pharmacy	Status	Delete Effective Date	Last Change	Agent Name	Manufacturer Name
2	J28-6164-06355-01	NCI-Non Coverage Item	3	PROSTOFORTE	(Ayruvedic) Prostoforte	Combination	Capsules	50s	62.5	50.65	1.25	1.01	Active		8-Aug-18	NUPAL REMEDIES TRADING	New Udaya Pharmacy & Ayurvedic Laboratories, INDIA
3	T96-7632-07527-01	NCI-Non Coverage Item	3	MAGNESIUM PHOSPHORICUM 6X- ADLER PHARMA	(Hemeopathy) Magnesium Phosphoricum	250 mg	Tablets	200s	48	36	0.24	0.18	Active		10-Nov-19	ULTRAPHARMA DRUG STORE	Adler Pharma Produktion und Vertrieb GmbH, AUSTRIA
4	D98-2596-00001-01	NCI-Non Coverage Item	3	IRICAR	(Homeopathy)	Combination	Cream	50g	48	36	48	36	Active		8-Jun-16	ULTRA MEDICAL STORE (ULTRAMED)	Deutsche Homoopathie Union, GERMANY
Sheet1

almaita.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	New Drug Code	Insurance Plan	New Tier	Package Name	Generic Name	Strength	Dosage Form	Package Size	Package Price to Public	Package Price to Pharmacy	Unit Price to Public	Unit Price to Pharmacy	Status	Delete Effective Date	Last Change	Agent Name	Manufacturer Name
2	J28-6164-06355-01	NCI-Non Coverage Item	2	PROSTOFORTE	(Ayruvedic) Prostoforte	Combination	Capsules	50s	62.5	50.65	1.25	1.01	Active		8-Aug-18	NUPAL REMEDIES TRADING	New Udaya Pharmacy & Ayurvedic Laboratories, INDIA
3	T96-7632-07527-01	NCI-Non Coverage Item	3	MAGNESIUM PHOSPHORICUM 6X- ADLER PHARMA	(Hemeopathy) Magnesium Phosphoricum	250 mg	Tablets	200s	60	36	1	0.18	Active		10-Nov-19	ULTRAPHARMA DRUG STORE	Adler Pharma Produktion und Vertrieb GmbH, AUSTRIA
4	G32-0766-00004-01	NCI-Non Coverage Item	3	CALMVALERA HEVERT	(Homeopathy) (CALMVALERA HEVERT)	Combination	Oral Solution	50ml	72	54	72	54	Active		27-Nov-18	ULTRA MEDICAL STORE (ULTRAMED)	Hevert-Arzneimittel GmbH & Co. KG, GERMANY
Sheet2

almaita.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	New Drug Code	Insurance Plan	New Tier	Package Name	Generic Name	Strength	Dosage Form	Package Size	Package Price to Public	Package Price to Pharmacy	Unit Price to Public	Unit Price to Pharmacy	Status	Delete Effective Date	Last Change	Agent Name	Manufacturer Name
2	J28-6164-06355-01	NCI-Non Coverage Item	2	PROSTOFORTE	(Ayruvedic) Prostoforte	Combination	Capsules	50s	62.5	50.65	1.25	1.01	Active		8/08/2018	NUPAL REMEDIES TRADING	New Udaya Pharmacy & Ayurvedic Laboratories, INDIA
3	T96-7632-07527-01	NCI-Non Coverage Item	3	MAGNESIUM PHOSPHORICUM 6X- ADLER PHARMA	(Hemeopathy) Magnesium Phosphoricum	250 mg	Tablets	200s	60	36	1	0.18	Active		10/11/2019	ULTRAPHARMA DRUG STORE	Adler Pharma Produktion und Vertrieb GmbH, AUSTRIA
4	G32-0766-00004-01	NCI-Non Coverage Item	3	CALMVALERA HEVERT	(Homeopathy) (CALMVALERA HEVERT)	Combination	Oral Solution	50ml	72	54	72	54	Active		27/11/2018	ULTRA MEDICAL STORE (ULTRAMED)	Hevert-Arzneimittel GmbH & Co. KG, GERMANY
5	D98-2596-00001-01	NCI-Non Coverage Item	3	IRICAR	(Homeopathy)	Combination	Cream	50g	48	36	48	36	Active		8/06/2016	ULTRA MEDICAL STORE (ULTRAMED)	Deutsche Homoopathie Union, GERMANY
Report

 

[EFANYoutube]

The only part I think should be added, but only if you use a new sheet everytime new data comes in. Is for there a way for the user to select the old sheet and the new sheet. You can do this be loading up a user form with 2 combo boxes and an OK button