Solved

Max Version

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

Suggested Solutions

Title # Comments Views Activity
Turn a spreadsheet into a vba executable. 2 102
sumHeights2  challenge 7 137
SQUD PROXY SERVER, UNIX, SLL/HTTPS 5 132
Capture logon name 13 116
Here is how to use MFC's automatic Radio Button handling in your dialog boxes and forms.  Beginner programmers usually start with a OnClick handler for each radio button and that's just not the right way to go.  MFC has a very cool system for handli…
If you use Adobe Reader X it is possible you can't open OLE PDF documents in the standard. The reason is the 'save box mode' in adobe reader X. Many people think the protected Mode of adobe reader x is only to stop the write access. But this fe…
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 an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

738 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