This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I stole a page out of @danilang 's book and just have a rather lengthy multi-row formula tool that is looking back at the previous rows...200 of them! haha so it's not entirely dynamic in that you'd have change your MR Formula depending on your data. I only needed like 170 rows for my data, so I just did 200 to add some margin. And I did use excel to create that formula. It still processes in a second or 2.
Here's where the magic happens, something like 23,000 characters in that Multi-Row Formula tool checking the prior 200 rows:
Recipe: - Keep track of the pairs - manage the sets of pairs rather than the full string - To figure out the counts of the individual characters - just split up the pairs; then add the first and last character - then divide the counts by 2.
Ahhhhh finally got Part 2!! Took several days of noodling on it, and scrolling through tips from Community + ACEs (I think @afv2688 was the first to point me in the right direction, THANK YOU!!), but it finally clicked.
My macro simply takes all the pairs, maps them to the new character and then makes two new pairs, one with the first character +inserted character, second with the right hand character+inserted character.
Group by pairs and outputs a count and then send it back through again.