0

I have a set of source data that looks something like this:

Project Series  Paper
Unit 1  1806    1
Unit 1  1806    2
Unit 1  1806    3
Unit 2  1903    1
Unit 2  1903    2
Unit 2  2003    1
Unit 2  2003    2
Unit 2  2103    1
Unit 2  2103    2
Unit 3  1806    1
Unit 3  1906    1

This data normally lives in a database and is huge. In the order of half a million rows.

We also have users that will input a combination of Project, Series and Paper then they will click submit.

Prior to the data being submitted, I would like the data to be validated from the source data and will tell the user if the combination they have entered is valid or not.

Something like this:

Project Series  Paper   Valid?
Unit 1  1806    1       No
Unit 2  1906    2       Yes

The easiest solution that I can think of is to concatenate the data and do a lookup on each. However, this will create unnecessary heavy load on the database, where a new column will have to be created with half a million rows of data...

I was wondering if there is a loop function in VBA that would check the combination from the source data and let the user know if it is valid or not?

I really appreciate your input.

Oday Salim
  • 1,129
  • 3
  • 23
  • 46
  • 2
    How do you decide what is valid and what isn't? – John Bustos Jun 01 '18 at 15:10
  • And, also, is there a reason for using Excel for this over just doing all this directly in the database? – John Bustos Jun 01 '18 at 15:11
  • So one is valid, the other not, but based on what criteria as neither exist already... – Solar Mike Jun 01 '18 at 15:20
  • There's no need to concatenate: use " ...where col1=val1 and col2=val2 and col3=val3" (pseudosql) – Tim Williams Jun 01 '18 at 15:23
  • @JohnBustos - To decide what is valid and what is not is by looking at what is at the source data. For instance, Unit 1 is only available in 1806 and has papers 1, 2 and 3. Therefore, if a user inputs Unit 1 in any other series or any other papers, the combination will become invalid. The reason why we are using Excel is because we need to have a UI for users to put in the combination, and then we check if the combination is valid or not from a data source that is stored in teh database. In addition, you are correct, I made a mistake on Unit 1 in the example which is fixed now. – Oday Salim Jun 01 '18 at 18:45
  • Why not create a stored procedure on the DB, pass in the values, and have the SP ensure data combo's are valid? Or read the valid combos into an ADO database and validate vs that (haven't done VBA forever ... is it .NET abled now?) – HardCode Jun 01 '18 at 19:01

1 Answers1

1

Ideally, this should be done in your SQL update and check for Primary Key conflicts, but, to do this in Excel, you could use the CountIfs function and check if you have any matches in your dataset.

So, suppose you have your DB table in range, say A1:C500000 and you have your input checker values in cells F2:H2, you could use the following formula for your Valid? in cell I2:

I2: =IF(COUNTIFS(A1:A500000,F2,B1:B500000,G2,C1:C500000,H2)=0,"Yes", "No")

That should do the trick.

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • That is amazing. May I pick your brain, how would you store such a procedure on SQL? Thanks – Oday Salim Jun 01 '18 at 19:06
  • Well, since you know ahead of time that you want uniqueness in the 3 columns, set a composite primary key on all 3 columns. If you tried to insert a row that had those 3 values already existing, your DB would kick back an error. You then just report it to your user... Here's just one quick example of setting up a composite primary key: https://stackoverflow.com/a/3922379/1693085 – John Bustos Jun 01 '18 at 19:11
  • Also, since you're new to SO, don't forget to mark this as your answer if it did solve your problem so others can know it will help them too in the future... Good luck!! – John Bustos Jun 01 '18 at 19:12