# 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-conformities1.1 Wrong Size 11.2 Wrong Color 32.1 Wrong Material 12.2. Component A Incorrectly Installed 22.3 Component B Incorrectly Installed 0I 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 ObservationI 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

#1
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 •

#2
November 19, 2010 at 15:37:25
 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.