Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Max Version

Posted on 2015-02-16
4
Medium Priority
?
93 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 1500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction: Dialogs (1) modal - maintaining the database. Continuing from the ninth article about sudoku.   You might have heard of modal and modeless dialogs.  Here with this Sudoku application will we use one of each type: a modal dialog …
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.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Suggested Courses

730 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