Problem with formula
Closed
tai8
Posts
2
Registration date
Monday June 12, 2017
Status
Member
Last seen
June 13, 2017
-
Jun 12, 2017 at 05:25 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 13, 2017 at 11:23 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 13, 2017 at 11:23 AM
Related:
- Problem with formula
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Date formula in excel dd/mm/yyyy - Guide
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Credit summation formula - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jun 13, 2017 at 11:05 AM
Jun 13, 2017 at 11:05 AM
Hi tai8,
Your query doesn't make much sense to me.
You want to compare column A to column B, when record found check column C. When column C is empty or zero, then the record is not found, you want to return an 'x'. When column C is not empty (does it matter if it found the record?), you want to empty the cell.
So you want to loop through column C and replace all empty and zero cells by 'x' and clear all the cells that have a record which is found in both column A and B.
Is this what you are after? Am I even close?
Best regards,
Trowa
Your query doesn't make much sense to me.
You want to compare column A to column B, when record found check column C. When column C is empty or zero, then the record is not found, you want to return an 'x'. When column C is not empty (does it matter if it found the record?), you want to empty the cell.
So you want to loop through column C and replace all empty and zero cells by 'x' and clear all the cells that have a record which is found in both column A and B.
Is this what you are after? Am I even close?
Best regards,
Trowa
tai8
Posts
2
Registration date
Monday June 12, 2017
Status
Member
Last seen
June 13, 2017
Jun 13, 2017 at 11:12 AM
Jun 13, 2017 at 11:12 AM
You are almost there,
You want to compare column a record in column A to a range of records in column B, when record is found in column then check column C for that record. If column column C is empty or zero, then return an "X' . if column c is not empty or zero then leave it blank (does it matter if it found the record?), Yes, it does matter if the record is found. I want the solution to be posted in column E. I am making sense?
You want to compare column a record in column A to a range of records in column B, when record is found in column then check column C for that record. If column column C is empty or zero, then return an "X' . if column c is not empty or zero then leave it blank (does it matter if it found the record?), Yes, it does matter if the record is found. I want the solution to be posted in column E. I am making sense?
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jun 13, 2017 at 11:23 AM
Jun 13, 2017 at 11:23 AM
Let's see if we can get there all the way :)
Give the following code a try in a copy of your workbook:
Since you asked for a formula and instead get a macro code, here is how to use it:
How to implement and run a code:
- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.
Best regards,
Trowa
Give the following code a try in a copy of your workbook:
Sub RunMe()
Dim mFind As Range
For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Set mFind = Columns("B").Find(cell.Value)
If Not mFind Is Nothing Then
Set mFind = Columns("C").Find(cell.Value)
If Not mFind Is Nothing Then mFind.ClearContents
End If
Next cell
For Each cell In Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
If cell.Value = vbNullString Or cell.Value = 0 Then cell.Offset(0, 2).Value = "x"
Next cell
End Sub
Since you asked for a formula and instead get a macro code, here is how to use it:
How to implement and run a code:
- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.
Best regards,
Trowa
