Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare blank cells in a column
Hi
I am looking for some VBA to do the following please. I have 4 columns of data. Column D contains results of tests most of the columns have the word “Negative” some are blank (ie test has not been carried out yet) and some have a comment (free text). There is a Product number in column C. I want to be able to compare the blank cells to the cells with the word negative and identify any matches of product number just leaving the rows with blank cells which have a matched product number as follows. Date Item ProductNo Result 01/01/09 Screws 2345 Negative 02/01/09 Washers 13658 Negative 03/01/09 Bolts 15896 Negative 05/01/09 Screws 12345 09/01/09 Washers 13658 14/01/09 Bolts 15896 Not suitable 15/01/09 Spanners 56987 Negative Giving 05/01/09 Screws 12345 09/01/09 Washers 13658 I hope this is possible. Any help is greatly appreciated Eddie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare blank cells in a column
Sub RemoveRows() 'first sort data in column D LastRow = Range("A" & Rows.Count).End(xlUp).Row Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ key1:=Range("D1"), _ Order1:=xlAscending, _ header:=xlYes If Range("d2") < "" Then LastRow = Range("D2").End(xlDown).Row Rows("2:" & LastRow).Delete End If End Sub " wrote: Hi I am looking for some VBA to do the following please. I have 4 columns of data. Column D contains results of tests most of the columns have the word €śNegative€ť some are blank (ie test has not been carried out yet) and some have a comment (free text). There is a Product number in column C. I want to be able to compare the blank cells to the cells with the word negative and identify any matches of product number just leaving the rows with blank cells which have a matched product number as follows. Date Item ProductNo Result 01/01/09 Screws 2345 Negative 02/01/09 Washers 13658 Negative 03/01/09 Bolts 15896 Negative 05/01/09 Screws 12345 09/01/09 Washers 13658 14/01/09 Bolts 15896 Not suitable 15/01/09 Spanners 56987 Negative Giving 05/01/09 Screws 12345 09/01/09 Washers 13658 I hope this is possible. Any help is greatly appreciated Eddie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare blank cells in a column
On Apr 9, 12:16*pm, joel wrote:
Sub RemoveRows() 'first sort data in column D LastRow = Range("A" & Rows.Count).End(xlUp).Row Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ * *key1:=Range("D1"), _ * *Order1:=xlAscending, _ * *header:=xlYes If Range("d2") < "" Then * *LastRow = Range("D2").End(xlDown).Row * *Rows("2:" & LastRow).Delete End If End Sub " wrote: Hi I am looking for some VBA to do the following please. I have 4 columns of data. Column D contains results of tests most of the columns have the word “Negative” some are blank (ie test has not been carried out yet) and some have a comment (free text). There is a Product number in column C. I want to be able to compare the blank cells to the cells with the word negative and identify any matches of product number just leaving the rows with blank cells which have a matched product number as follows. Date * * * Item * * * * * *ProductNo * * * * * Result 01/01/09 * Screws * * * * * 2345 * * * Negative 02/01/09 * Washers * * * * * * * * *13658 * * *Negative 03/01/09 * Bolts * * * * * * 15896 * * Negative 05/01/09 * Screws * * * * * * 12345 09/01/09 * Washers * * * * * * * * * *13658 14/01/09 * Bolts * * * * * * *15896 * * * * * Not suitable 15/01/09 * Spanners * * * * * * * * * * * * * 56987 * * * * * *Negative Giving 05/01/09 * Screws * * * * *12345 09/01/09 * Washers * * * * * * * * *13658 I hope this is possible. Any help is greatly appreciated Eddie- Hide quoted text - - Show quoted text - Thanks joel Unfortunately this deletes all rows. It does not leave the rows where there is a match in product number in column C. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare blank cells in a column
My last macro ggave the same results you had posted. I went back and read
your instructions and found that you only want one result (13658). here is the modified code. Sub RemoveRows() 'first sort data in column C and D LastRow = Range("A" & Rows.Count).End(xlUp).Row Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ key1:=Range("C1"), _ Order1:=xlAscending, _ key2:=Range("D1"), _ Order2:=xlAscending, _ header:=xlYes 'put x in rows to delete For RowCount = 2 To LastRow If Range("D" & RowCount) < "" Or _ Range("C" & RowCount) < _ Range("C" & (RowCount - 1)) Then Range("E" & RowCount) = "X" End If Next RowCount 'sort deleted rows to top of sheet SortRange.Sort _ key1:=Range("E1"), _ Order1:=xlAscending, _ header:=xlYes If Range("E2") < "" Then LastRow = Range("E" & Rows.Count).End(xlUp).Row Rows("2:" & LastRow).Delete End If End Sub " wrote: On Apr 9, 12:16 pm, joel wrote: Sub RemoveRows() 'first sort data in column D LastRow = Range("A" & Rows.Count).End(xlUp).Row Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ key1:=Range("D1"), _ Order1:=xlAscending, _ header:=xlYes If Range("d2") < "" Then LastRow = Range("D2").End(xlDown).Row Rows("2:" & LastRow).Delete End If End Sub " wrote: Hi I am looking for some VBA to do the following please. I have 4 columns of data. Column D contains results of tests most of the columns have the word €śNegative€ť some are blank (ie test has not been carried out yet) and some have a comment (free text). There is a Product number in column C. I want to be able to compare the blank cells to the cells with the word negative and identify any matches of product number just leaving the rows with blank cells which have a matched product number as follows. Date Item ProductNo Result 01/01/09 Screws 2345 Negative 02/01/09 Washers 13658 Negative 03/01/09 Bolts 15896 Negative 05/01/09 Screws 12345 09/01/09 Washers 13658 14/01/09 Bolts 15896 Not suitable 15/01/09 Spanners 56987 Negative Giving 05/01/09 Screws 12345 09/01/09 Washers 13658 I hope this is possible. Any help is greatly appreciated Eddie- Hide quoted text - - Show quoted text - Thanks joel Unfortunately this deletes all rows. It does not leave the rows where there is a match in product number in column C. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare blank cells in a column
On Apr 9, 2:17*pm, joel wrote:
My last macro ggave the same results you had posted. *I went back and read your instructions and found that you only want one result (13658). *here is the modified code. Sub RemoveRows() 'first sort data in column C and D LastRow = Range("A" & Rows.Count).End(xlUp).Row Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ * *key1:=Range("C1"), _ * *Order1:=xlAscending, _ * *key2:=Range("D1"), _ * *Order2:=xlAscending, _ * *header:=xlYes 'put x in rows to delete For RowCount = 2 To LastRow * *If Range("D" & RowCount) < "" Or _ * * * Range("C" & RowCount) < _ * * * * *Range("C" & (RowCount - 1)) Then * * * Range("E" & RowCount) = "X" * *End If Next RowCount 'sort deleted rows to top of sheet SortRange.Sort _ * *key1:=Range("E1"), _ * *Order1:=xlAscending, _ * *header:=xlYes If Range("E2") < "" Then * *LastRow = Range("E" & Rows.Count).End(xlUp).Row * *Rows("2:" & LastRow).Delete End If End Sub " wrote: On Apr 9, 12:16 pm, joel wrote: Sub RemoveRows() 'first sort data in column D LastRow = Range("A" & Rows.Count).End(xlUp).Row Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ * *key1:=Range("D1"), _ * *Order1:=xlAscending, _ * *header:=xlYes If Range("d2") < "" Then * *LastRow = Range("D2").End(xlDown).Row * *Rows("2:" & LastRow).Delete End If End Sub " wrote: Hi I am looking for some VBA to do the following please. I have 4 columns of data. Column D contains results of tests most of the columns have the word “Negative” some are blank (ie test has not been carried out yet) and some have a comment (free text). There is a Product number in column C. I want to be able to compare the blank cells to the cells with the word negative and identify any matches of product number just leaving the rows with blank cells which have a matched product number as follows. Date * * * Item * * * * * *ProductNo * * * * * Result 01/01/09 * Screws * * * * * 2345 * * * Negative 02/01/09 * Washers * * * * * * * * *13658 * * *Negative 03/01/09 * Bolts * * * * * * 15896 * * Negative 05/01/09 * Screws * * * * * * 12345 09/01/09 * Washers * * * * * * * * * *13658 14/01/09 * Bolts * * * * * * *15896 * * * * * Not suitable 15/01/09 * Spanners * * * * * * * * * * * * * 56987 * * * * * *Negative Giving 05/01/09 * Screws * * * * *12345 09/01/09 * Washers * * * * * * * * *13658 I hope this is possible. Any help is greatly appreciated Eddie- Hide quoted text - - Show quoted text - Thanks joel Unfortunately this deletes all rows. It does not leave the rows where there is a match in product number in column C.- Hide quoted text - - Show quoted text - Thanks Joel This works perfect, one last thing. On the data I showed on my first message column D may contain blank cells the word "Negative" or Free text "such as "Not tested", if by some chance the following occurs I also get the free text row when the query is run . Date Item ProductNo Result 01/01/09 Screws 12345 Negative 02/01/09 Washers 13658 Negative 03/01/09 Bolts 15896 Negative 05/01/09 Screws 12345 09/01/09 Washers 13658 14/01/09 Bolts 15896 Not suitable 15/01/09 Spanners 56987 Negative 16/01/09 Bolts 15896 I now get 05/01/09 Screws 12345 09/01/09 Washers 13658 16/01/09 Bolts 15896 I would need 05/01/09 Screws 12345 09/01/09 Washers 13658 Therefore leaving out the "not suitable" match Hope this is possible You have been a great help to me today Thanks very much Eddie |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare blank cells in a column
Deleteing all Not suitable items became more difficult. I used a SUMPRODUCT
formula on the worksheet to help get this right. Sub RemoveRows() 'first sort data in column C and D LastRow = Range("A" & Rows.Count).End(xlUp).Row 'do Not suitable test 'if one item is not suitable marked all the 'items not suitable a = "=if(sumproduct(--(C$2:C$" & LastRow & "=" & C2 & "))" Range("E2").Formula = _ "=if(sumproduct(--(C$2:C$" & LastRow & "=C2)," & _ "--(D$2:D$" & LastRow & "=""Not Suitable""))0,""X"","""")" Range("E2").Copy _ Destination:=Range("E2:E" & LastRow) 'replace formula with values Range("E2:E" & LastRow).Copy Range("E2:E" & LastRow).PasteSpecial _ Paste:=xlPasteValues Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ key1:=Range("C1"), _ Order1:=xlAscending, _ key2:=Range("D1"), _ Order2:=xlAscending, _ header:=xlYes 'put x in rows to delete For RowCount = 2 To LastRow If Range("D" & RowCount) < "" Or _ Range("C" & RowCount) < _ Range("C" & (RowCount - 1)) Then Range("E" & RowCount) = "X" Else If Range("E" & RowCount) < "X" Then Range("E" & RowCount) = "" End If End If Next RowCount 'sort deleted rows to top of sheet SortRange.Sort _ key1:=Range("E1"), _ Order1:=xlAscending, _ header:=xlYes If Range("E2") < "" Then LastRow = Range("E" & Rows.Count).End(xlUp).Row Rows("2:" & LastRow).Delete End If End Sub " wrote: On Apr 9, 2:17 pm, joel wrote: My last macro ggave the same results you had posted. I went back and read your instructions and found that you only want one result (13658). here is the modified code. Sub RemoveRows() 'first sort data in column C and D LastRow = Range("A" & Rows.Count).End(xlUp).Row Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ key1:=Range("C1"), _ Order1:=xlAscending, _ key2:=Range("D1"), _ Order2:=xlAscending, _ header:=xlYes 'put x in rows to delete For RowCount = 2 To LastRow If Range("D" & RowCount) < "" Or _ Range("C" & RowCount) < _ Range("C" & (RowCount - 1)) Then Range("E" & RowCount) = "X" End If Next RowCount 'sort deleted rows to top of sheet SortRange.Sort _ key1:=Range("E1"), _ Order1:=xlAscending, _ header:=xlYes If Range("E2") < "" Then LastRow = Range("E" & Rows.Count).End(xlUp).Row Rows("2:" & LastRow).Delete End If End Sub " wrote: On Apr 9, 12:16 pm, joel wrote: Sub RemoveRows() 'first sort data in column D LastRow = Range("A" & Rows.Count).End(xlUp).Row Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ key1:=Range("D1"), _ Order1:=xlAscending, _ header:=xlYes If Range("d2") < "" Then LastRow = Range("D2").End(xlDown).Row Rows("2:" & LastRow).Delete End If End Sub " wrote: Hi I am looking for some VBA to do the following please. I have 4 columns of data. Column D contains results of tests most of the columns have the word €śNegative€ť some are blank (ie test has not been carried out yet) and some have a comment (free text). There is a Product number in column C. I want to be able to compare the blank cells to the cells with the word negative and identify any matches of product number just leaving the rows with blank cells which have a matched product number as follows. Date Item ProductNo Result 01/01/09 Screws 2345 Negative 02/01/09 Washers 13658 Negative 03/01/09 Bolts 15896 Negative 05/01/09 Screws 12345 09/01/09 Washers 13658 14/01/09 Bolts 15896 Not suitable 15/01/09 Spanners 56987 Negative Giving 05/01/09 Screws 12345 09/01/09 Washers 13658 I hope this is possible. Any help is greatly appreciated Eddie- Hide quoted text - - Show quoted text - Thanks joel Unfortunately this deletes all rows. It does not leave the rows where there is a match in product number in column C.- Hide quoted text - - Show quoted text - Thanks Joel This works perfect, one last thing. On the data I showed on my first message column D may contain blank cells the word "Negative" or Free text "such as "Not tested", if by some chance the following occurs I also get the free text row when the query is run . Date Item ProductNo Result 01/01/09 Screws 12345 Negative 02/01/09 Washers 13658 Negative 03/01/09 Bolts 15896 Negative 05/01/09 Screws 12345 09/01/09 Washers 13658 14/01/09 Bolts 15896 Not suitable 15/01/09 Spanners 56987 Negative 16/01/09 Bolts 15896 I now get 05/01/09 Screws 12345 09/01/09 Washers 13658 16/01/09 Bolts 15896 I would need 05/01/09 Screws 12345 09/01/09 Washers 13658 Therefore leaving out the "not suitable" match Hope this is possible You have been a great help to me today Thanks very much Eddie |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare blank cells in a column
On Apr 9, 8:13*pm, joel wrote:
Deleteing all Not suitable items became more difficult. *I used a SUMPRODUCT formula on the worksheet to help get this right. Sub RemoveRows() 'first sort data in column C and D LastRow = Range("A" & Rows.Count).End(xlUp).Row 'do Not suitable test 'if one item is not suitable marked all the 'items not suitable a = "=if(sumproduct(--(C$2:C$" & LastRow & "=" & C2 & "))" Range("E2").Formula = _ * *"=if(sumproduct(--(C$2:C$" & LastRow & "=C2)," & _ * *"--(D$2:D$" & LastRow & "=""Not Suitable""))0,""X"","""")" Range("E2").Copy _ * *Destination:=Range("E2:E" & LastRow) 'replace formula with values Range("E2:E" & LastRow).Copy Range("E2:E" & LastRow).PasteSpecial _ * *Paste:=xlPasteValues Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ * *key1:=Range("C1"), _ * *Order1:=xlAscending, _ * *key2:=Range("D1"), _ * *Order2:=xlAscending, _ * *header:=xlYes 'put x in rows to delete For RowCount = 2 To LastRow * *If Range("D" & RowCount) < "" Or _ * * * Range("C" & RowCount) < _ * * * * *Range("C" & (RowCount - 1)) Then * * * Range("E" & RowCount) = "X" * *Else * * * If Range("E" & RowCount) < "X" Then * * * * *Range("E" & RowCount) = "" * * * End If * *End If Next RowCount 'sort deleted rows to top of sheet SortRange.Sort _ * *key1:=Range("E1"), _ * *Order1:=xlAscending, _ * *header:=xlYes If Range("E2") < "" Then * *LastRow = Range("E" & Rows.Count).End(xlUp).Row * *Rows("2:" & LastRow).Delete End If End Sub " wrote: On Apr 9, 2:17 pm, joel wrote: My last macro ggave the same results you had posted. *I went back and read your instructions and found that you only want one result (13658). *here is the modified code. Sub RemoveRows() 'first sort data in column C and D LastRow = Range("A" & Rows.Count).End(xlUp).Row Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ * *key1:=Range("C1"), _ * *Order1:=xlAscending, _ * *key2:=Range("D1"), _ * *Order2:=xlAscending, _ * *header:=xlYes 'put x in rows to delete For RowCount = 2 To LastRow * *If Range("D" & RowCount) < "" Or _ * * * Range("C" & RowCount) < _ * * * * *Range("C" & (RowCount - 1)) Then * * * Range("E" & RowCount) = "X" * *End If Next RowCount 'sort deleted rows to top of sheet SortRange.Sort _ * *key1:=Range("E1"), _ * *Order1:=xlAscending, _ * *header:=xlYes If Range("E2") < "" Then * *LastRow = Range("E" & Rows.Count).End(xlUp).Row * *Rows("2:" & LastRow).Delete End If End Sub " wrote: On Apr 9, 12:16 pm, joel wrote: Sub RemoveRows() 'first sort data in column D LastRow = Range("A" & Rows.Count).End(xlUp).Row Set SortRange = Rows("1:" & LastRow) SortRange.Sort _ * *key1:=Range("D1"), _ * *Order1:=xlAscending, _ * *header:=xlYes If Range("d2") < "" Then * *LastRow = Range("D2").End(xlDown).Row * *Rows("2:" & LastRow).Delete End If End Sub " wrote: Hi I am looking for some VBA to do the following please. I have 4 columns of data. Column D contains results of tests most of the columns have the word “Negative” some are blank (ie test has not been carried out yet) and some have a comment (free text). There is a Product number in column C. I want to be able to compare the blank cells to the cells with the word negative and identify any matches of product number just leaving the rows with blank cells which have a matched product number as follows. Date * * * Item * * * * * *ProductNo * * * * * Result 01/01/09 * Screws * * * * * 2345 * * * Negative 02/01/09 * Washers * * * * * * * * *13658 * * *Negative 03/01/09 * Bolts * * * * * * 15896 * * Negative 05/01/09 * Screws * * * * * * 12345 09/01/09 * Washers * * * * * * * * * *13658 14/01/09 * Bolts * * * * * * *15896 * * * * * Not suitable 15/01/09 * Spanners * * * * * * * * * * * * * 56987 * * * * * *Negative Giving 05/01/09 * Screws * * * * *12345 09/01/09 * Washers * * * * * * * * *13658 I hope this is possible. Any help is greatly appreciated Eddie- Hide quoted text - - Show quoted text - Thanks joel Unfortunately this deletes all rows. It does not leave the rows where there is a match in product number in column C.- Hide quoted text - - Show quoted text - Thanks Joel This works perfect, one last thing. On the data I showed on my first message column D may contain blank cells the word "Negative" or Free text "such as "Not tested", if by some chance the following occurs I also get the free text row when the query is run . Date * *Item * * * * * * * * * * * * ProductNo * * * * * Result 01/01/09 * * * *Screws * * * * * 12345 * * * * * * * * *Negative 02/01/09 * * * *Washers * * * * 13658 * * * * * * *Negative 03/01/09 * * * *Bolts * * * * * * 15896 * * * * * * *Negative 05/01/09 * * * *Screws * * * * * 12345 09/01/09 * * * *Washers * * * * 13658 14/01/09 * * * *Bolts * * * * * * *15896 * * * * * Not suitable 15/01/09 * * * *Spanners * * * *56987 * * * * * *Negative 16/01/09 * * * *Bolts * * * * * * *15896 I now get 05/01/09 * * * *Screws * * * * * 12345 09/01/09 * * * *Washers * * * * 13658 16/01/09 * * * *Bolts * * * * * * *15896 I would need 05/01/09 * * * *Screws * * * * * 12345 09/01/09 * * * *Washers * * * * 13658 Therefore leaving out the "not suitable" match Hope this is possible You have been a great help to me today Thanks very much Eddie- Hide quoted text - - Show quoted text - Hi Joel I meant to get back earlier, this works beautifully. Thank you for all your help and patience with this problem. Eddie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare 2 column cells and return the adjacent columns cells data of the cell | Excel Worksheet Functions | |||
Compare 1 cell to column of cells returning adjacent cells info? | Excel Worksheet Functions | |||
compare cells in column to criteria, then average next column cell | Excel Worksheet Functions | |||
compare cells in a column for duplicates | Excel Programming | |||
How do I compare cells in a column | New Users to Excel |