Help needed with VBA Code to generate report

D

dsubash

New Member
Joined
Nov 22, 2024
Messages
30
Office Version
  1. 2019
  2. Prefer Not To Say
Platform
  1. Windows
Hi Experts,

Need some VBA Code for my reports. Given below the screenshot of my database and reports page. I have used normal excel formulas to derive at this report. But it takes too much time to generate and sometimes excel hangs up.

Sheet1 (Data)
Sample Test Details TDM.xlsx
ABCDEFGHIJKLMNOPQ
1No.Sample No.Sample IDTestResultRegister DateNameAgeGenderCase No.Bed No.DoctorDepartmentDiagnosisRemarkCheckerTest Date
211062339000106233900 UREA2901/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:13
321062339000106233900 SGOT19.601/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:16
431062339000106233900 GGT24.601/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:17
541062339000106233900 TP5.1501/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:17
651062339000106233900 CHOL16901/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:15
761062339000106233900 BILL-D0.3201/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:16
871062339000106233900 ALP7001/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:16
981062339000106233900 BILL-T101/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:15
1091062339000106233900 CAL A11.301/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:14
11101062339000106233900 TGL13701/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:15
12111062339000106233900 SGPT2501/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:16
13121062339000106233900 CREATIN1.0301/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:18
14131062339000106233900 ALB4.0301/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:17
15141062339000106233900 UA5.1801/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:14
16151062339000106233900 GLUC11201/01/2025 08:57NithishKumar0YearsMale01/01/2025 09:14
17161062341000106234100 PHOS4.1201/01/2025 08:57Maharaja0YearsMale01/01/2025 09:21
18171062341000106234100 UA6.301/01/2025 08:57Maharaja0YearsMale01/01/2025 09:20
19181062341000106234100 TGL15101/01/2025 08:57Maharaja0YearsMale01/01/2025 09:21
20191062341000106234100 UREA5401/01/2025 08:57Maharaja0YearsMale01/01/2025 09:19
21201062341000106234100 CHOL19101/01/2025 08:57Maharaja0YearsMale01/01/2025 09:20
22211062341000106234100 GLUC11601/01/2025 08:57Maharaja0YearsMale01/01/2025 09:19
23221062341000106234100 CAL A10.401/01/2025 08:57Maharaja0YearsMale01/01/2025 09:20
24231062341000106234100 CREATIN2.2501/01/2025 08:57Maharaja0YearsMale01/01/2025 09:21
25241062340000106234000 CREATIN0.8601/01/2025 08:58VetrivelS0YearsMale01/01/2025 09:19
26251062340000106234000 CHOL12001/01/2025 08:58VetrivelS0YearsMale01/01/2025 09:18
27261062340000106234000 TGL19201/01/2025 08:58VetrivelS0YearsMale01/01/2025 09:19
28271062340000106234000 GLUC40201/01/2025 08:58VetrivelS0YearsMale01/01/2025 09:18
29281062342000106234200 GLUC9701/01/2025 09:59AnandKumarV0YearsMale01/01/2025 10:13
30295005736000500573600 GLUC15001/01/2025 10:19John0YearsMale01/01/2025 10:34
31305005735000500573500 GLUC9401/01/2025 10:19Pooranima0YearsFemale01/01/2025 10:33
32315005735010106234400 GLUC10101/01/2025 10:29logapriyaMale01/01/2025 10:49
33325005735020106234400 UA3.601/01/2025 10:36logapiyaMale01/01/2025 10:50
34335005735020106234400 SGPT2301/01/2025 10:36logapiyaMale01/01/2025 10:52
35345005735020106234400 ALB3.9701/01/2025 10:36logapiyaMale01/01/2025 10:53
36355005735020106234400 BILL-T0.2801/01/2025 10:36logapiyaMale01/01/2025 10:51
37365005735020106234400 TP5.2901/01/2025 10:36logapiyaMale01/01/2025 10:53
38375005735020106234400 TGL23101/01/2025 10:36logapiyaMale01/01/2025 10:51
Data


Sheet2(Report)
Sample Test Details TDM.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1Sl. No.Sample IDRegister DateNameGenderTest DateUREASGOTGGTTPCHOLBILL-DALPBILL-TCAL ATGLSGPTCREATINALBUAGLUCPHOSACEIRONAMYLASEMICROCKNACMAGLDHCKMBZINC-CO
210106233900 01/01/2025 08:57NithishKumarMale01/01/2025 09:13UREASGOTGGTTPCHOLBILL-DALPBILL-TCAL ATGLSGPTCREATINALBUAGLUC          
320106234100 01/01/2025 08:57MaharajaMale01/01/2025 09:21UREA   CHOL   CAL ATGL CREATIN UAGLUCPHOS         
430106234000 01/01/2025 08:58VetrivelSMale01/01/2025 09:19    CHOL    TGL CREATIN  GLUC          
540106234200 01/01/2025 09:59AnandKumarVMale01/01/2025 10:13              GLUC          
650500573600 01/01/2025 10:19JohnMale01/01/2025 10:34              GLUC          
760500573500 01/01/2025 10:19PooranimaFemale01/01/2025 10:33              GLUC          
870106234400 01/01/2025 10:29logapriyaMale01/01/2025 10:49UREASGOT TPCHOLBILL-DALPBILL-TCAL ATGLSGPTCREATINALBUAGLUC          
980106234104 01/01/2025 11:27MaharajaMale01/01/2025 11:41              GLUC          
1090106234004 01/01/2025 11:27VetrivelSMale01/01/2025 11:40              GLUC          
11100500573604 01/01/2025 12:00johnMale01/01/2025 12:15              GLUC          
12110500573504 01/01/2025 12:01pooranimaMale01/01/2025 12:15              GLUC          
13120106234204 01/01/2025 12:02anandkumarMale01/01/2025 12:15              GLUC          
14130106235600 01/01/2025 12:41AbdulAlphafMale01/01/2025 12:54UREASGOTGGTTP BILL-DALPBILL-T  SGPTCREATINALB            
Report
Cell Formulas
RangeFormula
A2:A14A2=IF(LEN(B2)>0,SUM(A1)+1,"")
B2:B3B2=IFERROR(INDEX(Data!$C$2:$C$10000, MATCH(0, COUNTIF($B$1:B1, Data!$C$2:$C$10000), 0)), "")
C2:C14C2=IFERROR(VLOOKUP(B2, Data!$C:$Q, 4, 0),"")
D2:D14D2=IFERROR(VLOOKUP(B2,Data!$C$2:$Q$10000,5,0),"")
E2:E14E2=IFERROR(VLOOKUP(B2,Data!$C$2:$Q$10000,7,0),"")
F2:F14F2=IFERROR(VLOOKUP(B2, Data!$C$2:$Q$10000, 15, 0), "")
G2:G14G2=IF(COUNTIFS(Data!$C:$C, $B2, Data!$D:$D, G$1) > 0, G$1, "")
H2:AE14H2=IF(COUNTIFS(Data!$C$2:$C$10000, $B2, Data!$D$2:$D$10000, H$1) > 0, H$1, "")
B4:B14B4=IFERROR(INDEX(Data!C4:C962, MATCH(0, COUNTIF($B$1:B3, Data!C4:C962), 0)), "")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
_FilterDatabase=Report!$A$1:$AE$1128A2


Help with a VBA Code to generate report would be of great help to me. My database runs to numerous rows and excel formula takes too much time to process the data.

Thanks in advance
Subash D
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Keeping it simple without vba you could create a helper column on your data sheet combining sampleid and test
Example 0106233900UREA in say column Z
then on your heavy lifting section with the multiple COUNTIFS
Try replacing with a MATCH to lookup the key so starting with column G
=IF(ISNUMBER(MATCH(B2&G$1,DATA!$Z$1:$Z$10000,0)),G$1,””)
Drag down and across and see if the speed increases
 
Upvote 0
Solution
Column Z would be On the Data sheet =C2&D2
 
Upvote 0
jimrward said:
Column Z would be On the Data sheet =C2&D2
Click to expand...
Thanks for the formula, but it returns #Name? error if there is no data matching the header

Sample Test Details TDM.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1Sl. No.Sample IDRegister DateNameGenderTest DateUREASGOTGGTTPCHOLBILL-DALPBILL-TCAL ATGLSGPTCREATINALBUAGLUCPHOSACEIRONAMYLASEMICROCKNACMAGLDHCKMBZINC-CO
210106233900 01/01/2025 08:57NithishKumarMale01/01/2025 09:13UREASGOTGGTTPCHOLBILL-DALPBILL-TCAL ATGLSGPTCREATINALBUAGLUC#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
320106234100 01/01/2025 08:57MaharajaMale01/01/2025 09:21UREA#NAME?#NAME?#NAME?CHOL#NAME?#NAME?#NAME?#NAME?TGL#NAME?#NAME?#NAME?UAGLUCPHOS#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
430106234000 01/01/2025 08:58VetrivelSMale01/01/2025 09:19#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
540106234200 01/01/2025 09:59AnandKumarVMale01/01/2025 10:13#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
650500573600 01/01/2025 10:19JohnMale01/01/2025 10:34#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
760500573500 01/01/2025 10:19PooranimaFemale01/01/2025 10:33#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
870106234400 01/01/2025 10:29logapriyaMale01/01/2025 10:49#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
980106234104 01/01/2025 11:27MaharajaMale01/01/2025 11:41#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
1090106234004 01/01/2025 11:27VetrivelSMale01/01/2025 11:40#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
11100500573604 01/01/2025 12:00johnMale01/01/2025 12:15#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
Report
Cell Formulas
RangeFormula
A2:A11A2=IF(LEN(B2)>0,SUM(A1)+1,"")
B2:B3B2=IFERROR(INDEX(Data!$C$2:$C$10000, MATCH(0, COUNTIF($B$1:B1, Data!$C$2:$C$10000), 0)), "")
C2:C11C2=IFERROR(VLOOKUP(B2, Data!$C:$Q, 4, 0),"")
D2:D11D2=IFERROR(VLOOKUP(B2,Data!$C$2:$Q$10000,5,0),"")
E2:E11E2=IFERROR(VLOOKUP(B2,Data!$C$2:$Q$10000,7,0),"")
F2:F11F2=IFERROR(VLOOKUP(B2, Data!$C$2:$Q$10000, 15, 0), "")
G2:G11G2=IF(ISNUMBER(MATCH(B2&G$1,Data!$Z$1:$Z$10000,0)),G$1,””)
H2:H11H2=IF(ISNUMBER(MATCH(B2&H$1,Data!$Z$1:$Z$10000,0)),H$1,””)
I2:I11I2=IF(ISNUMBER(MATCH(B2&I$1,Data!$Z$1:$Z$10000,0)),I$1,””)
J2:J11J2=IF(ISNUMBER(MATCH(B2&J$1,Data!$Z$1:$Z$10000,0)),J$1,””)
K2:K11K2=IF(ISNUMBER(MATCH(B2&K$1,Data!$Z$1:$Z$10000,0)),K$1,””)
L2:L11L2=IF(ISNUMBER(MATCH(B2&L$1,Data!$Z$1:$Z$10000,0)),L$1,””)
M2:M11M2=IF(ISNUMBER(MATCH(B2&M$1,Data!$Z$1:$Z$10000,0)),M$1,””)
N2:N11N2=IF(ISNUMBER(MATCH(B2&N$1,Data!$Z$1:$Z$10000,0)),N$1,””)
O2:O11O2=IF(ISNUMBER(MATCH(B2&O$1,Data!$Z$1:$Z$10000,0)),O$1,””)
P2:P11P2=IF(ISNUMBER(MATCH(B2&P$1,Data!$Z$1:$Z$10000,0)),P$1,””)
Q2:Q11Q2=IF(ISNUMBER(MATCH(B2&Q$1,Data!$Z$1:$Z$10000,0)),Q$1,””)
R2:R11R2=IF(ISNUMBER(MATCH(B2&R$1,Data!$Z$1:$Z$10000,0)),R$1,””)
S2:S11S2=IF(ISNUMBER(MATCH(B2&S$1,Data!$Z$1:$Z$10000,0)),S$1,””)
T2:T11T2=IF(ISNUMBER(MATCH(B2&T$1,Data!$Z$1:$Z$10000,0)),T$1,””)
U2:U11U2=IF(ISNUMBER(MATCH(B2&U$1,Data!$Z$1:$Z$10000,0)),U$1,””)
V2:V11V2=IF(ISNUMBER(MATCH(B2&V$1,Data!$Z$1:$Z$10000,0)),V$1,””)
W2:W11W2=IF(ISNUMBER(MATCH(B2&W$1,Data!$Z$1:$Z$10000,0)),W$1,””)
X2:X11X2=IF(ISNUMBER(MATCH(B2&X$1,Data!$Z$1:$Z$10000,0)),X$1,””)
Y2:Y11Y2=IF(ISNUMBER(MATCH(B2&Y$1,Data!$Z$1:$Z$10000,0)),Y$1,””)
Z2:Z11Z2=IF(ISNUMBER(MATCH(B2&Z$1,Data!$Z$1:$Z$10000,0)),Z$1,””)
AA2:AA11AA2=IF(ISNUMBER(MATCH(B2&AA$1,Data!$Z$1:$Z$10000,0)),AA$1,””)
AB2:AB11AB2=IF(ISNUMBER(MATCH(B2&AB$1,Data!$Z$1:$Z$10000,0)),AB$1,””)
AC2:AC11AC2=IF(ISNUMBER(MATCH(B2&AC$1,Data!$Z$1:$Z$10000,0)),AC$1,””)
AD2:AD11AD2=IF(ISNUMBER(MATCH(B2&AD$1,Data!$Z$1:$Z$10000,0)),AD$1,””)
AE2:AE11AE2=IF(ISNUMBER(MATCH(B2&AE$1,Data!$Z$1:$Z$10000,0)),AE$1,””)
B4:B11B4=IFERROR(INDEX(Data!C4:C962, MATCH(0, COUNTIF($B$1:B3, Data!C4:C962), 0)), "")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
_FilterDatabase=Report!$A$1:$AE$1128A2
 
Upvote 0
Try changing the double quotes at the end of the isnumber line, I used the quotes on my iPad which might be different edit and use the ones of your keyboard
 
Upvote 0
dsubash said:
Works fine, thanks Jim
Click to expand...
=IFERROR(INDEX(Data!$C$2:$C$10000, MATCH(0, COUNTIF($B$1:B1, Data!$C$2:$C$10000), 0)), "")

Is there any way wherein Instead of providing randing row numbers "Data!$C$2:$C$10000", the formula automatically works till the last row. Though i have given row 10000 as the end row, my data rows would either increase or decrease.

I tried using Data!$C:$C, but it takes a lot of time to calculate
 
Upvote 0
I believe that some versions of excel are smart enough to workout the last row, otherwise it gets a bit messy
You can create a named range which shrinks and grows accordingly but it would use volatile functions which need recalculating each time so it will diminish your speed accordingly
Using a whole column reference like C:C is definitely very slow
Another option is to convert your original data to a table and update your formulas accordingly and this will shrink and grow automatically
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA code to transpose data from vertical to horizontal based on certain conditions.
Replies
2
Views
68
dsubash
D
D
  • Question Question
Task Management spreadsheet. How to create a column where i can assign the task to several people (not just one)
Replies
2
Views
53
HighAndWilder
H
M
  • Solved
Filter Function: Return single row while looking at entire array?
Replies
3
Views
85
Cubist
Cubist
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
96
Dazzybeeguy
D
Z
  • Question Question
VBA: Date Format Issues with Data Validation
Replies
3
Views
171
zero269
Z

Forum statistics

Threads
1,225,635
Messages
6,186,128
Members
453,340
Latest member
Stu61

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top