Solved

Max Version

Posted on 2015-02-16
4
92 Views
Last Modified: 2015-02-24
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
Comment
Question by:Ganesh Vijaykumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 500 total points
ID: 40612175
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
 

Author Comment

by:Ganesh Vijaykumar
ID: 40614092
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40614261
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
 

Author Comment

by:Ganesh Vijaykumar
ID: 40627731
Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question