CODEWITHSUNDEEP
excelvba
veblen
veblen

Reputation: 73

Sum of rows with multiple conditions in Excel VBA

I need to be able to do a summary of rows based on certain column conditions. There's a table with the following columns:

ID# (row #) 
Part
Customer 
Job 
QTY 
Dept 
Pass/Fail

Where ID# can possibly be the only unique value.

From the table the following needs to be obtained:

  1. Need to return All jobs.
  2. Need to return all new jobs, which should be all jobs minus any duplicates (i.e., the first entry for each unique Part-Customer-Job-QTY-Dept (i.e., where all 5-values are equal).
  3. All Repeat jobs, which should be all ID#-Part-Customer-Job-QTY-Dept-Pass.

For example: 

1-Part;Customer;Job; QTY; Dept; Fail
2-Part; Customer; Job; QTY; Dept; Pass

Where Part-Customer-Job-QTY-Dept are equal, with 1 failing and 2 passing.

1 and 2 are easy, but 3 is a little tricky.

Should I just find the ID#'s (rows) that include a fail prior to a pass.

Can it be done in a single loop?

While I'm here, #2 might be tricky as well. Is there any easy way to sum, without duplicates?

Any help will be highly appreciated!

Please let me know if any additional info is needed.

The sample data below should return:

10 for All

8 for New

2 for Repeats

Doing the sample has me thinking if just subtracting New from All will return all repeats.

enter image description here

Upvotes: 0

Views: 254

Answers (1)

devildelta
devildelta

Reputation: 135

I am not sure if you actually need to use VBA as the question seems can be solved by default functions.

The key problem here is to identify all the repeating records, of the unique key Part-Customer-Job-QTY-Dept. Note that you don't actually need to take care of the ID and Pass/Fail as these values do not affect your calculation.

Once you know the unique key, you can solve by the following steps:

  1. Make a new column concatenates to produce the unique key. (F1)=A1&B1&C1&D1&E1
  2. For each row, count the appearance of the unique key among the column.(G1)=countif(F:F,F1)
  3. You can determine the record is duplicating when the count is larger than one, meaning there are multiple lines in the data using the same unique key.=countif(G:G,">1")
  4. Once you have the Yes/No answer on each row, you simply count the Yes to yield the repeating count, thus the new jobs according to your definition.

This can also be implemented in VBA by the same logic.

Upvotes: 2

Related Questions