Solved

Max Version

Posted on 2015-02-16
4
86 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to calculate times for developing software? 8 65
Include multiple hostnames in this command? 3 53
how to monitor remote shell execution on linux 9 97
Not needed 13 93
Introduction: Ownerdraw of the grid button.  A singleton class implentation and usage. Continuing from the fifth article about sudoku.   Open the project in visual studio. Go to the class view – CGridButton should be visible as a class.  R…
Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
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.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now