Forum Discussion
Can I assign two different values for consecutive rows with the same exact identifying data
Your proposed formula may be close, but you are not telling us why your COUNTIF is looking for a more specific value ("120672-000.1") than your SEARCH does ("120672").
If we assume that was a false start, consider this formula instead:=IF(ISNUMBER(SEARCH("120672",$H198)), IF(MOD(COUNTIF($H$1:$H198, "*120672*"),2)=1, "LLC1", "LLC2"), "")It is the use of the asterisk wildcard characters that allows the COUNTIF to do a partial string match. Note that for consistency, I included a $ in front of a couple more column H references. This formula intentionally counts occurrences of descriptions with the identifier from the top just through the current row, not through row 1000. Also note that the occurrences of the identifier do not need to be in adjacent rows for this to work.
Your text implies that your data will never have more than two occurrences of the identifier. If the data might have more, you may want to reconsider the use of MOD, and just test the COUNTIF result directly.
You also are not telling us which version of Excel you are using, on which platform (Windows/Mac/web). Fortunately in this case, that appears to be unimportant. But if you have Excel 2021 or later, you may find the LET function useful for breaking a calculation into parts, and making it easier to examine the results of those parts.
Your proposed formula may be close, but you are not telling us why your COUNTIF is looking for a more specific value ("120672-000.1") than your SEARCH does ("120672").
If we assume that was a false start, consider this formula instead:
=IF(ISNUMBER(SEARCH("120672",$H198)), IF(MOD(COUNTIF($H$1:$H198, "*120672*"),2)=1, "LLC1", "LLC2"), "")It is the use of the asterisk wildcard characters that allows the COUNTIF to do a partial string match. Note that for consistency, I included a $ in front of a couple more column H references. This formula intentionally counts occurrences of descriptions with the identifier from the top just through the current row, not through row 1000. Also note that the occurrences of the identifier do not need to be in adjacent rows for this to work.
Your text implies that your data will never have more than two occurrences of the identifier. If the data might have more, you may want to reconsider the use of MOD, and just test the COUNTIF result directly.
You also are not telling us which version of Excel you are using, on which platform (Windows/Mac/web). Fortunately in this case, that appears to be unimportant. But if you have Excel 2021 or later, you may find the LET function useful for breaking a calculation into parts, and making it easier to examine the results of those parts.
- This works! Thank you so much for your help!
