Re: Enter value 2 columns to right of cell address reference

[Chris2010]

Re: Enter value 2 columns to right of cell address reference

Hi Everyone,

Here is my situation.

I really hope someone will be able to guide me in the right direction as spent like 2 days trying to workout the solution.

My Specification

O/S: Windows 8.1
Programs: Microsoft Excel 2013

Problem / Task

My very basic spreadsheet allows me to check the location of new email addresses entered to see if they already exist on that sheet followed by returning the cell reference. When it fails to find a match it returns "#N/A". As you can see my skills are limited when it comes to formulas in Excel.

I need Excel to check the cell reference found in "Column M" each time. As long as it returns a proper cell reference rather than "#N/A" we proceed by putting "U" in the very next column from that cell reference in the same row. As you can imagine it would prove a timely process to use "Find" facility on a sheet with 100,000 emails or having to scroll to that cell reference each time.

I would imagine to achieve this will probably require VBA coding although hopefully possible a formula?

Here is the setup

File: Master.xlsx
Sheet: Sheet3

Column A: ID
Column B: Email Address
Column C: Status
Column D: Details
Column E: Ref
Column F: Assigned
Column G: Type 2Column H: Type 3
Column I: Duplicate (Records entered here checked against column B for a match)
Column J: Cell Reference
Column K: Type 4 (Returns an "X" if match found in same row, to 2 columns to right of cell reference returned in Column I)

My spreadsheet is setup to return the location (cell reference) of an email address on the same sheet (sheet3).

Sample Data

A	B	C	D	E	F	G	H	I	J	K
ID	Email Address	Status	Details	Ref	Assigned	Type 2	Type 3	Duplicate	Cell Ref	Type 4
1	test@test.com	Strong	Science	None	Yes	1	No	apple@test.com	$C$3
2	apple@test.com	Medium	IT	None	No	1	Yes	test@test.com	$C$2
3	sample@test.com	Strong	IT	R12345	Pending	5	Yes	test2@test.com	#N/A

<tbody>
</tbody>

Formulas

J2 is:
=CELL("address",INDEX($B:$I,MATCH(I2,$B:$B,0),2))

J3 is:
=CELL("address",INDEX($B:$I,MATCH(I3,$B:$B,0),2))

J4 is:
=CELL("address",INDEX($B:$I,MATCH(I4,$B:$B,0),2))

Expected Result

ID	Email Address	Status	Details	Ref	Assigned	Type 2	Type 3	Duplicate	Cell Ref	Type 4
1	test@test.com	Strong	Science	None	Yes	1	No	apple@test.com	$C$3	U
2	apple@test.com	Medium	IT	None	No	1	Yes	test@test.com	$C$2	U
3	sample@test.com	Strong	IT	R12345	Pending	5	Yes	test2@test.com	#N/A

<tbody>
</tbody>

I would be delighted to hear from anyone that can guide me in the right direction. A macro or just simple formulas will be fine with me. I still have this feeling it can be achieved with a formula, but will let you experts out there confirm whether that is possible.

Thank you so much in advance!

Best Wishes,

Chris

 

[CyrusTheVirus]

Re: Enter value 2 columns to right of cell address reference

I don't see column M mentioned in your table, was that a mistake?

I need Excel to check the cell reference found in "Column M" each time.

And what is the difference between these bottom two quotes?

As long as it returns a proper cell reference rather than "#N/A" we proceed by putting "U" in the very next column from that cell reference in the same row...

Column K: Type 4 (Returns an "X" if match found in same row, to 2 columns to right of cell reference returned in Column I)

 

[Chris2010]

Re: Enter value 2 columns to right of cell address reference

Hi CyrusTheVirus,

I don't see column M mentioned in your table, was that a mistake?

This was a mistake. It should have said "Column J".

And what is the difference between these bottom two quotes?

Once again another mistake. There is no difference, it should say show "X" when a cell reference is found.

Sorry for the confusion.

Cheers,

Chris

 

[CyrusTheVirus]

Re: Enter value 2 columns to right of cell address reference

Hi Chris,

So are you just wanting a formula for Column K or Column J, or both?

Do I understand you right that you are wanting a formula for Column K that populate an 'X' if there is a cell reference found in the cell to the left of it, but you don't want anything to populate if there is an error to the left of it?

If so, then maybe the below code could work?

=IF(ISERROR($J2),"","X")

ID	Email Address	Status	Details	Ref	Assigned	Type 2	Type 3	Duplicate	Cell Ref	Type 4
1	test@test.com	Strong	Science	None	Yes	1	No	apple@test.com	$C$3	X
2	apple@test.com	Medium	IT	None	No	1	Yes	test@test.com	$C$2	X
3	sample@test.com	Strong	IT	R12345	Pending	5	Yes	test2@test.com	#N/A

<colgroup><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Chris2010]

Re: Enter value 2 columns to right of cell address reference

Hi Cyrus,

I posted my formula for Column J in case this needed to be modified. You will fully understand why after this post now.

This is partially correct, but it's the actual cell reference address of the string returned and not the cell reference.

Where it shows J2 returned address "$C$3" - so were focusing on reference "C3" and wanting to put an "X" in K3.

As you know J3 returned "$C$2" so we focus on "C2" and put an "X" in K2.

So you could say it's 8 columns to right of Column C or 1 column to right of Column J (Cell reference).

Finally, we have J4 and this returned "#N/A" meaning nothing happens. No matches found.

Hopefully that makes more sense?

Sorry again for the confusion

Cheers

Chris

 

[CyrusTheVirus]

Re: Enter value 2 columns to right of cell address reference

Hi Chris,

I'm sorry, but i'm not fully understanding.

Question 1: Which column do you need your formula in?
Question 2: Why does J2 say C3, why do you want to reference a cell address that has 'Strong' in it?

 

[Chris2010]

Re: Enter value 2 columns to right of cell address reference

Hi Cyrus,

Sorry for all of last night's confusion. I shouldn't have posted this thread whilst in a sleep deprived state.

Question 1: Which column do you need your formula in?

I require a formula to run down Column K. It returns the actual location (cell address) of any duplicate email address found when checked against Column B. When this happens, I want it to return an "X" on the same row as the cell address, but in Column K.

A valid cell reference in my view is anything other than "#N/A". This was obviously poor work on my part as "#N/A" means no duplicates were found. I would have preferred to leave this blank when it found no duplicate records as "#N/A" is very messy.

Question 2: Why does J2 say C3, why do you want to reference a cell address that has 'Strong' in it?

This is where it gets even crazier. I don't actually want it to return Column C in the cell address, but it was the closest formula could come up with to get to my desired outcome. All I wanted to do was compare emails on each row of Column I against the same row on Column B to find duplicates. When a duplicate is found it marks this row on Column K with an "X" otherwise it leaves it completely blank.

I take it I have used the wrong formula in Col J making things far too complicated???

Cheers,

Chris