Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

Max Version

I am trying to update “Y” or “N” based on the maximum number in a excel spreadsheet. Put differently and more specifically, I have 200000 quotes and have to update “Y” against the latest quote.
For E.g. I have 3 columns named “Quote ID”; “Quote Version”; Max Version. As shown in the below table I have to update “Y” against the maximum number under column quote Version
Quote Id             Quote Version        Max Ver
P1-C102678              3                                 N
P1-C102678             4                              N
P1-C102678             5                                Y

Similarly when data gets refreshed and an additional line item is added the max version status should change (only if the latest quote has maximum number)

Quote Id                            Quote Version                 Max Ver
P1-C102678                                3                                   N
P1-C102678                                4                                   N
P1-C102678                             5                                   N
P1-C102678                                7                                   Y

I have tried using below mentioned “Maxif” function and it’s working absolutely fine for 20000 line items. But, the only problem is the performance issue if it is more than 20000 lines excel is getting crashed.
Function:{ =MAX(IF(B:B=B2,C:C))}
I am looking for alternatives like creating a database in Access or anything that meets my exact needs and improve the performance

Any suggestions would be greatly appreciated!

I have uploaded a word doc with examples

I am looking forward for the positive response!!!!!!

Thanks,
Ganesh
Max-version.docx
0
Ganesh Vijaykumar
Asked:
Ganesh Vijaykumar
  • 2
  • 2
1 Solution
 
AndyAinscowCommented:
An Access database should be suitable.  
You can have a form as 'endless' to provide you with a long list.  As long as you index the fields to sort and filter on the latest quote will be very rapid.
0
 
Ganesh VijaykumarAuthor Commented:
Hi AndyAinscow,

Thanks for the suggestion!

I am pretty new to the MS access database. It would be great if you could upload a sample database.

Looking forward for your response!

Thanks,
Ganesh
0
 
AndyAinscowCommented:
That is a little bit of work to create a sample DB.  The experts here give their time voluntarily.  You can often get more information about an expert by clicking their name, some are even available for hire, but can't explicitly ask for it or advertise themselves directly.
0
 
Ganesh VijaykumarAuthor Commented:
Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now