Can I assign two different values for consecutive rows with the same exact identifying data

[Michael_Falcone]

I'm trying to automate a monthly task that's within a large data set. In Column H, which is the description column, there are two consecutive cells that both contain the same exact 6 digits (120672) within the greater description. (There is no other unifying data in the description that can be used to create this formula). I need to create a uniform formula to the right of Column H that will return one value (LLC1) from the first of two cells that contains the 6 digits (120672) in the description and will return another value (LLC2) from the second of two descriptions that contains the 6 digits (120672). 

In my current month report, these two consecutive cells occur on rows 198 and row 199 of my data set. Below is what I thought it should be for the first instance, but this is not working. 

=IF(ISNUMBER(SEARCH("120672",H198)), IF(MOD(COUNTIF($H$1:H1000, "120672-000.1"),2)=1, "LLC1", "LLC2"), "")

*I've been working with ChatGPT on this for several hours to no avail. The AI keeps assuming that I am trying to count the number of instances the 6 digits exist. Again, I am trying to create a formula so that I can assign different values for the first and second occurrence of the exact same 6 digits within a description. Any help would be greatly appreciated. 

[#2 · Accepted solution]

@Michael_Falcone 

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.

[#3]

This works! Thank you so much for your help!