Offset Function help

[tpezzati]

Hey all,

I'm a pretty basic user of excel and am having a rough go of a problem that needs solving. I have a list of names located in Column A, like jack, jill, bob, tom. Some of which are duplicates. How do I use the OFFSET function to mark the first occurrence of a name with a 1, and any other with a 0? So it looks like this.

Bob 1
Bob 0
Jenny 1
Jenny 0
Jenny 0
Tom 1
Tom 0
Tom 0

I think I conceptually understand how the Offset function works, though it seems as though it needs to be used in conjunction with other functions, maybe countif???

Any help is appreciated.

 

[jasonb75]

Welcome to the board!

No offset needed, just countif

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Bob</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Bob</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Jenny</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Jenny</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Jenny</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Tom</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Tom</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Tom</TD><TD style="TEXT-ALIGN: right">0</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=--(COUNTIF(A$1:A1,A1)=1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Just enter the formula once and use autofill to do the rest.

 

[tpezzati]

Thank you for the quick response. However, in the question itself it stresses the use of the offset function and rather than finding the "best solution" to the problem. Is there a way to incorporate the OFFSET function when doing this?

 

[jasonb75]

If you're trying to use this as a way to improve your understanding of the OFFSET function, this is not really the best way to do it.

There is certainly no solution to your question using the offset function that comes to mind. Generally speaking it would be used to return a range to a formula that is relative to a given criteria.

Try searching "excel offset" on google and looking at a few examples, or search offset on the forum to see what other questions come up and look at the solutions provided.

Not always, but more often than not there is a more efficient alternative to offset.

 

[tpezzati]

I've looked online for solutions using the offset function to no avail. This question is designed to be a part of excel exercises for an internship I'm interested in. It does seem as though offset is completely unnecessary for this question, but why would they ask that I use the function in the first place?

 

[jasonb75]

Based on the assumption that there is NOT a name in row 1 of the sheet, you could use

=--(OFFSET(A2,-1,0)<>A2)

which is the same as

=IF(OFFSET(A2,-1,0,1,1)<>A2,1,0)

but with a couple of "shortcuts"

 

[snoopyhr]

Here is my 2 cents on this, my guess is whoever gave you this exercise wanted to see if the concept of offset is understood. that is creating a range within a formula instead of referring to range, this approach comes handy in the situation where you need to lookup a value but you are not allowed to sort the table under no circumstances.

here is my solution.

=IF(COUNTIF(OFFSET($A$1,,,ROWS($A$1:A1)),A1)=1,1,0)

hope this helped.

 

[tpezzati]

Snoopy thank you, that's precisely the concept that this excel exercise was focusing on.

Honestly though my biggest frustration is that I want to UNDERSTAND this and what I am doing. I have never utilized excel for these types of functions. How do i get a better understanding of what I am doing, as opposed to just copying and pasting the formula and getting the correct answer? I think i understand the concept of what offset itself does on its own. But i can't wrap my head around nesting other functions together with it. I get confused quickly.

 

[Trouttrap2]

The offset function takes some time to understand - it did for me. There are some great videos about the offset from ExcelIsFun Mike Girvin a contributor on this board.

http://www.youtube.com/user/ExcelIsFun#p/search/0/JhfQXv9sJpA

 

[jasonb75]

Try single clicking the cell with the formula, then press Alt M V

click evaluate to follow the formula calculation step by step.

Snoopy's formula has 4 functions

=IF(COUNTIF(OFFSET($A$1,,,ROWS($A$1:A1)),A1)=1,1,0)

this show's how they nest, they calculate from the centre, outwards, so ROWS() provides a value to OFFSET, which provides a range to COUNTIF, which creates a value for the logical test of the IF function to determine the result.

For information, the "shortcut" method I used earlier could also be used here.

=IF(logical test,1,0) is the same as

=--(logical test)

In logical terms, TRUE = 1, FALSE = 0, the logical test on it's own would return TRUE or FALSE, but =--() forces the numeric equivilant.

Hope that helps.