Combined IF and SORTING Excel Formulas

Microsoft Office excel 2007 home & stude...
November 19, 2010 at 14:37:53
Specs: Windows XP
Trying to write a report template.
Column A includes data referencing an internal violation. Column B lists the text of that violation. Data for Columns A and B will be unchanged or locked and the user will rate the severity and enter a number based upon the severity of the observation ( i.e. column C = 0, 1, 2, or 3), where 0 = in compliance and 3 = major non-conformities

1.1 Wrong Size 1
1.2 Wrong Color 3
2.1 Wrong Material 1
2.2. Component A Incorrectly Installed 2
2.3 Component B Incorrectly Installed 0

I am looking to have the formula calculate in Column D the law reference and Column E the corresponding Description that gets the most severe Violation and sort the violations. So in my example above Columns D and E would be calculated

1.2 Wrong Color
2.2. Component A Incorrectly Installed
1.1 Wrong Size
2.1 Wrong Material
2.3 Component B Incorrectly Installed

and if possible, I would prefer to have 2.3 be blank since it was rated 0 or in compliance, so it automatically sorts descending from 3 to 1 only. And if possible in my best case scenario I would like the data in Columns D, E and F to appear as such (even on a separate worksheet)

1.2 Wrong Color Major NonConformance
2.2. Component A Incorrectly Installed Minor NonConformance
1.1 Wrong Size Minor NonConformance
2.1 Wrong Material Observation

I hope I have explained my dilemna adequately. Am I asking too much? And I truly appreciate any assistance that can be provided.

See More: Combined IF and SORTING Excel Formulas

Report •

November 19, 2010 at 14:53:42
I should add Columns A and B will have as many as 100 questions and that is why I am hoping to automate the sorting

Report •

November 19, 2010 at 15:37:25
While I work on a possible solution, I have a few comments about your post.

1 - The next time you need to post data in this forum, please read the How To referenced in my signature line. It will help you line up your example data to make it easier for us to read.

2 - In your example, both 1.1 and 2.1 were rated a 1. However, in your final result, 1.1 was listed as a Minor NonConformance but 2.1 was listed as an Observation.

If we're going to base the output on the rating alone, a "1" can't mean 2 different things. If there are more than one criteria that need to be considered, you'll need to tell us that.

3 - This is going to require a macro. There is no Excel function that will sort data. There are a few "ranking" functions that might let us put the numbers in order, but pulling the data associated with each number won't work since all of the look-up functions available only find the first occurrence of a value. e.g. if we search for a 3, it just keep returning the values associated with the first 3 it finds.

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

November 19, 2010 at 17:01:36
Try this code in a backup copy of your workbook. Macros can not be undone.

I assumed your data is in Sheet1 and that you have Column labels in Row 1.

e.g. something like this:

     A        B                      C
1   V#   Description             User Input
2  1.1    Wrong Size                 1
3  1.2    Wrong Color                3
4  2.1    Wrong Material             1
5  2.2.   A Incorrectly Installed    2
6  2.3    B Incorrectly Installed    0  

If that's not the layout you have, then the code will need to be modified since it copies the column labels from A1:C1 to D1:F1 and then sorts the data starting in Row 2.

I also assumed:

0  Clear Cells
1  Observation
2  Minor Non-Conformance
3  Major Non-Conformance 

Here's the code:

Option Explicit
Sub SortConformanceData()
Dim lastRow, rw As Integer
Dim c As Range
  With ActiveWorkbook.Worksheets(1)
'Find Last Row With Data
   lastRow = .Range("A" & Rows.Count).End(xlUp).Row
'Copy Data from Column A:C to Columns D:F
    .Range("A1:C" & lastRow).Copy Destination:=.Range("D1")
'Sort Columns D:F based on values in Column F
   .Range("D2:F" & lastRow).Sort _
          Key1:=Range("F2"), Order1:=xlDescending, _
          Key2:=Range("D2"), Order2:=xlAscending, _
          Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
'Replace Numbers with Text
    .Range("F2:F" & lastRow).Replace What:="3", Replacement:="Major Non-Conformance"
    .Range("F2:F" & lastRow).Replace What:="2", Replacement:="Minor Non-Conformance"
    .Range("F2:F" & lastRow).Replace What:="1", Replacement:="Observation"
'Clear Range with Zeros
     Set c = .Range("F2:F" & lastRow).Find(0)
      .Range("D" & c.Row & ":F" & lastRow).ClearContents
     End With
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

Related Solutions

Ask Question