Excel Guru Help - I will pay you... (1 Viewer)

[hotdogcollars]

Trying to get a count of matched pairs (words) in an excel file. I'll beg and then pay anyone who could quickly whip up a formula or macro to do this. Only has to be done once so the solution doesn't have to be pretty. Example

COL 1 | COL 2 | COL 3

Leash | Tag |

Leash | Tag | Collar

Collar | Tag |

Tag | Collar | Leash

Tag | Leash

So Tag-Leash count would be 4

Tag-Collar = 3

Leash-Collar = 2

The part that is tripping me up is that it has to count reverse pairs the same as the original sequence. ie Tag-Collar is the same as Collar-Tag. And I have 7 columns and 29,000 rows to count.

any excel gurus looking for a challenge?

 

[Long Ball Larry]

How many potential values are there in the cells?

My initial thought would be to create additional columns wit some kind of if statement that assigns a number to each word (if(a1="Tag",1) and so on.

Then the sum of those cells would tell you what the total combination must be.

Something like that.

 

[BroadwayG]

Just pairs or you want triples and quads and quints also?

 

[eoMMan]

If reverse pairs are throwing you off, maybe sort it in a way so this isn't an issue. For example, your above example has collar tag....can you sort them so it says tag collar instead to make your count easier?

 

[hotdogcollars]

LBL - 6000 unique products that could potentially appear in any given cell. which is why i think it has to be a macro and not formula

Broadway G - just pairs

 

[hotdogcollars]

If reverse pairs are throwing you off, maybe sort it in a way so this isn't an issue. For example, your above example has collar tag....can you sort them so it says tag collar instead to make your count easier?

sort in rows first and then column? I think it would still need a macro due to the 6000 unique "products" that could appear in any cell

i've managed to give myself a headache over this today and i'm usually pretty good when it comes to quick/dirty excel solutions

 

[BroadwayG]

Any duplicates to worry about? Collar|Collar|Tag?

 

[hotdogcollars]

Any duplicates to worry about? Collar|Collar|Tag?

they will exist and will ultimately be ignored. So the macro can either be smart enough to ignore it OR i can just filter them out of the final results

 

[hotdogcollars]

I think I'm just going to brute force this thing using a combination of formulas, manual copy/pasta, and pivot table'ing

 

[BroadwayG]

Got a quick macro running, but seems like you have it under control.

Still unsure how you want the duplicate handled though. Would you want Collar|Collar|Tag count Collar|Tag twice?

 

[hotdogcollars]

aaand done. not perfect, but close enough. thx to everyone for the thought and time

 

[hotdogcollars]

Got a quick macro running, but seems like you have it under control.

Still unsure how you want the duplicate handled though. Would you want Collar|Collar|Tag count Collar|Tag twice?

whoa, impressive. you do excel work for pay or just kicks? I definitely could use some excel help from time to time, and have a budget for it.

 

[BroadwayG]

Got a quick macro running, but seems like you have it under control.

Still unsure how you want the duplicate handled though. Would you want Collar|Collar|Tag count Collar|Tag twice?

whoa, impressive. you do excel work for pay or just kicks? I definitely could use some excel help from time to time, and have a budget for it.

I've been doing ad-hoc programming for most of my career.

 

[hotdogcollars]

Got a quick macro running, but seems like you have it under control.

Still unsure how you want the duplicate handled though. Would you want Collar|Collar|Tag count Collar|Tag twice?

whoa, impressive. you do excel work for pay or just kicks? I definitely could use some excel help from time to time, and have a budget for it.

I've been doing ad-hoc programming for most of my career.

if you're interested, PM me your email and i'll be in touch. Actually have a mtg later this week to discuss some excel work needed by my office manager. Was going to do it myself, but I am admittedly slowwww when it comes to writing macros