Return values from column based on condition

[truerip]

This is probably very simple but is giving me a problem.
I want to retrieve values from column B of Sheet2 to column C of Sheet1 while excluding cells that contain certain text (up to 4 different text strings).
Any suggestions would be gratefully appreciated.
Best regards,
truerip

 

[bellman101]

When you say excluding cells, do you mean display them as blank, or cut them out entirely, bumping the rest up 1?

 

[truerip]

I'm looking to cut them out entirely, bumping the rest up 1?

 

[bellman101]

I'm assuming you want this to be an automated process, rather than something you carry out manually?

VBA would be your best bet.

If you store your text strings you want to exclude in another location, then you can use it as a look up, rather than having loads of if statements. I've used Sheet3 Column A, but you can use anything you like, just adjust the Sheet3!A:A code accordingly.

Try this:

Sub MoveCertainCells()
    
    varROW = 1 'change this number to whatever row your data starts on.
    
    For each forCELL in Sheets("Sheet2").Range("B" & varROW & ":B" & Cells(Rows.Count,2).End(xlUp).row).cells 'Sets up the loop, from varROW above, down to the last used row of column B.
         If Evaluate("=COUNTIF(Sheet3!A:A," & forCELL & ")") = 0 then 'Performs a COUNTIF formula, checking if the value appears in your separate list of look up values mentioned above.
             Sheets("Sheet1").Range("C" & varROW) = forCELL 'If the above formula resolved to zero, then the content is transferred.
             varROW = varROW+1 'Move on to the next row.  Note, if the IF statement is not TRUE, then varROW does not increase, as we need to bump everything up a row.
         End If
    Next

End Sub

Hope that gets you close to what you need.

Cheers
JB

 

[truerip]

Thank you JB!
I created a "test" workbook with three sheets to test it out.
I placed the code (copied & pasted exactly) in Sheet1, the data in Sheet2, and left Sheet3 blank so as to test if it will get everything from Sheet2 column B...will add exclusion text later

Note: For testing, I placed the data in Sheet2 B8:B67

When I run it the code, it only returns the first item from Sheet2 and places it in Sheet1 row 13 of column C. It then stops.
I must be missing something. Ideas?
Thank you.

 

[bellman101]

Try this:

Sub MoveCertainCells()
    
    varROW = 8 'change this number to whatever row your data starts on.
    varDestRow = 2
    For Each forCELL In Sheets("Sheet2").Range("B" & varROW & ":B" & Cells(Rows.Count, 2).End(xlUp).Row).Cells
         If Evaluate("=COUNTIF(Sheet3!A:A," & forCELL.Value & ")") = 0 Then
             Sheets("Sheet1").Range("C" & varDestRow) = forCELL.Value
             varROW = varROW + 1
             varDestRow = varDestRow + 1
         End If
    Next


End Sub

 

[truerip]

JB,
It is still not wanting to cooperate with me and only returning the first value and stopping.
In my test workbook, it is only returning the value "Standard" from Sheet2.
Row 9 is blank but even if populated, it only returns "Standard".
During my test, I have left Sheet3!A:A blank so as not to exclude anything.
In the end, I am hoping to excluding cells that contain certain text, such as "COM", "NUC", etc.
Here is a portion of the data contained in Sheet2:

Excel 2013/2016
	A	B
1
2
3
4
5
6
7
8		Standard
9
10		BAL-001-TRE-1
11		BAL-005-0.2b
12		CIP-002-5.1a
13		CIP-003-6
14		CIP-004-6
15		CIP-005-5
16		CIP-006-6
17		CIP-007-6
18		CIP-008-5
19		CIP-009-6
20		CIP-010-2
21		CIP-011-2
22		COM-001-3
23		COM-002-4
24		EOP-004-3
25		EOP-005-2
26		FAC-001-2
27		FAC-002-2
28		FAC-003-4
29		FAC-008-3
30		NUC-002
Sheet2

 

[bellman101]

Hmm, well it works for me. Try stepping through the code, and check the variable values on each loop, to make sure they are behaving. After the 1st loop, does it ever go inside the If statement again, or does it just skip over it?

 

[truerip]

Stepped through the code as you recommended (F8). It does go into the IF statement for each cell with a value in Sheet2 column B then ends as it should.
So it is working, but in my workbook it is only returning "Standard" on Sheet1 C9.
There must be some setting that I am not aware of. What could I look for?

 

[bellman101]

That is really odd.

Try entering this below the line Sheets("Sheet1").Range("C" & varDestRow) = forCELL.Value

msgbox Sheets("Sheet1").Range("C" & varDestRow).address
msgbox forCELL.Value

Step through it again, the first line will show you a message box with the address of the cell it is trying to update. The second will show the value it is trying to set it to.

Are they as expected? so on the 3rd loop it should be: "$C$4" and "BAL-001-TRE-1" respectively...?