Microsoft Excel - How often do you use it?

[Coach]

For advanced users, tell us some of your tricks in terms of using the formulas and such.

For beginners, ask questions here, hopefully answers could be provided.

As for me, my question to the experts/advanced users,

Let's say if I have a spreadsheet with column A that has names, typically last name, first name and middle initial (example: Smith, Joe Steve). Column B has names that is typically the same as column A, except it would be missing the middle name or a misspelling on the first name (example: Smith, Joe).


Column A would have say 450 names and Column B would have 150. How can a "near" match search be found, after utilizing the conditional formatting that highlights duplicate values?

[scho63]

PUTTING OUT THE BAT SIGNAL FOR KCCROW AND C3HIEF3S PLUS ANY OTHER EXCEL EXPERTS.

I'm migrating a ton of dirty data and other crap records that are NOT connected from one shitty CRM (Agile) into Zoho and I have some questions.

Any assistance will be greatly appreciated and I may even be able to send a gift if extensive help.

Thank you in advance.

[ChiefGator]

Originally Posted by scho63

PUTTING OUT THE BAT SIGNA...

Feel free to PM if you want.

[digger]

Originally Posted by scho63

PUTTING OUT THE BAT SIGNAL FOR KCCROW AND C3HIEF3S PLUS ANY OTHER EXCEL EXPERTS.



I'm migrating a ton of dirty data and other crap records that are NOT connected from one shitty CRM (Agile) into Zoho and I have some questions.



Any assistance will be greatly appreciated and I may even be able to send a gift if extensive help.



Thank you in advance.

Clean your data...

Sent from my SM-G960U using Tapatalk

[scho63]

Originally Posted by ChiefGator

Feel free to PM if you want.

I'll shoot you a PM shortly once I finish a few tasks. Greatly appreciated

[scho63]

PM sent ChiefGator

[HoneyBadger]

Originally Posted by scho63

PUTTING OUT THE BAT SIGNAL FOR KCCROW AND C3HIEF3S PLUS ANY OTHER EXCEL EXPERTS.

I'm migrating a ton of dirty data and other crap records that are NOT connected from one shitty CRM (Agile) into Zoho and I have some questions.

Any assistance will be greatly appreciated and I may even be able to send a gift if extensive help.

Thank you in advance.

Have you tried using Alteryx yet? This is what it is made for.

[scho63]

Originally Posted by HoneyBadger

Have you tried using Alteryx yet? This is what it is made for.

Thank you for the thought and idea. Not what is needed here.

Once I get the info into Zoho, the Analytics there are strong as hell for our needs.

What I need is to connect records that were NOT connected as they came out of Agile CRM. First time in 9 CRMs I've used over 21 years that didn't have connected records and also lookups to connect missing info.

[kccrow]

Not sure what you're looking for. Seems mostly like an export CSV from Agile, manipulate to meet Zoho standard, then import CSV to Zoho type of deal but let me know precisely what you're looking for and I may be able to offer a suggestion.

[scho63]

Originally Posted by kccrow

Not sure what you're looking for. Seems mostly like an export CSV from Agile, manipulate to meet Zoho standard, then import CSV to Zoho type of deal but let me know precisely what you're looking for and I may be able to offer a suggestion.

When CRM data is exported from Salesforce or Zoho or Oracle on Demand or Netsuite, there are multiple tables that the info resides in, each one doing it a little different.

As an example, the "ACCOUNTS" table would come out with all the fields and in one of the columns would be the UNIQUE ID that would be titled "CONTACT ID" that would then be related to that CONTACT record in the "CONTACTS" table. You also might have a column in the "ACCOUNTS" table that would be a column called "OPPORTUNITY ID" that would link to any opportunities (deals) in the "OPPORTUNITY" table. Same goes for the very large "ACTIVITIES" table which has a column called "RELATED TO" and in that column are the UNIQUE IDs that could be related to a CONTACT, LEAD, ACCOUNT OR OPPORTUNITY.

Agile had NO LINKED UNIQUE ID'S between the tables so I need to find a way to link the COMPANIES to the DEALS to the CONTACTS in the three separate tables.

We have spent 2 weeks cleaning the data the best we can.

I'm down to finding a way to link them and what I may do is upload on of the tables into Zoho, immediately export all the data and use Zoho's UNIQUE ID to begin the connection. I then need a function or formula to connect EXACT companies names to the other tables names and that should work.

I also need to find a way to merge dupes without deleting the correct information when two records, such as an email address has two records and not sure which one to select.

Hope that makes sense...........

[digger]

You'll have better luck putting this data into a SQL table (or Access ick)... just my opinion

[kccrow]

Sorry, I have been less than responsive and hardly check in lately... I've been a single dad since January, am starting a new job, and do part-time work on the side for another company. My slate is full.

So, with that said... I'm not sure if you've solved your issue or not. If so, great and don't bother reading on...

First glance tells me you should export the data to CSV as I had mentioned, add the Unique ID column you need and create the Unique IDs. I'm not sure if the 3 separate data sets contain some connecting identifier, but I'd assume they do. It's been a while since I played with Agile but I'm almost certain of it. Best case there is merge the 3 sets of data as sheets in the same workbook, use VLOOKUPs to reference your newly created Unique ID in the one data set by whatever the connecting identifier is to cross over the Unique ID to all data sets.

If you need some help, PM me and I can get you an Email to send something to and we can figure it out.

[scho63]

Originally Posted by kccrow

Sorry, I have been less than responsive and hardly check in lately... I've been a single dad since January, am starting a new job, and do part-time work on the side for another company. My slate is full.

So, with that said... I'm not sure if you've solved your issue or not. If so, great and don't bother reading on...

First glance tells me you should export the data to CSV as I had mentioned, add the Unique ID column you need and create the Unique IDs. I'm not sure if the 3 separate data sets contain some connecting identifier, but I'd assume they do. It's been a while since I played with Agile but I'm almost certain of it. Best case there is merge the 3 sets of data as sheets in the same workbook, use VLOOKUPs to reference your newly created Unique ID in the one data set by whatever the connecting identifier is to cross over the Unique ID to all data sets.

If you need some help, PM me and I can get you an Email to send something to and we can figure it out.

Thanks for the advice-not always easy to check.

I had 4 separate sheets in 1 workbook already in cvs format.

I just got off the phone with a dedicated migration member from Zoho who is going to link all this shit together.

Four issues:

1. Company wasn't using Best Practices and used NOTES in AGILE CRM instead of completed tasks. No dates of completion or created date showed up upon export so ALL activities have to be tied together with both the USER and LEAD with UNIQUE IDS

2. Agile CRM sucks cock and doesn't provide relational database info so the three starting worksheets have no info to tie them together, a real cluster****.

3. Agile combines LEADS and CONTACTS all together as CONTACTS, making a ****ing mess of trying to market and work leads versus Accounts and Contacts in all other CRMS.

4. We are moving away from TAGS as everyone just made all kinds of TAGS, 295 worth and they are all mixed fields that have no ability to report on nor track. All the info like LEAD SOURCE, WHERE OBTAINED, TRADE SHOW, DEMO FORM and mixed info have been created in multiple correct fields for the right categories.

All in all a real ****ing mess with 35000 records for contacts and another 30000 for accounts and only a 1,000 deals.

Not much fun but I'm having ZOHO migration do it all as part of the onboarding process!!!

[kccrow]

Originally Posted by scho63

Thanks for the advice-not always easy to check.

I had 4 separate sheets in 1 workbook already in cvs format.

I just got off the phone with a dedicated migration member from Zoho who is going to link all this shit together.

Four issues:

1. Company wasn't using Best Practices and used NOTES in AGILE CRM instead of completed tasks. No dates of completion or created date showed up upon export so ALL activities have to be tied together with both the USER and LEAD with UNIQUE IDS

2. Agile CRM sucks cock and doesn't provide relational database info so the three starting worksheets have no info to tie them together, a real cluster****.

3. Agile combines LEADS and CONTACTS all together as CONTACTS, making a ****ing mess of trying to market and work leads versus Accounts and Contacts in all other CRMS.

4. We are moving away from TAGS as everyone just made all kinds of TAGS, 295 worth and they are all mixed fields that have no ability to report on nor track. All the info like LEAD SOURCE, WHERE OBTAINED, TRADE SHOW, DEMO FORM and mixed info have been created in multiple correct fields for the right categories.

All in all a real ****ing mess with 35000 records for contacts and another 30000 for accounts and only a 1,000 deals.

Not much fun but I'm having ZOHO migration do it all as part of the onboarding process!!!

Thankfully they are willing to take on the mess!

I've used Agile but very limitedly and only for a very small company that basically wanted to track leads, but the software did allow you to assign Contacts to Deals and then assign some % status of completion all the way to Closed. I'm wondering if people didn't use that feature in your particular instance? Or does Agile not export that piece of data?

[Mr. Plow]

Got a question that I'm sure a lot of you will know the answer to, but I'm needing some help. Here is what I've got.

Column A = Day of week (ie. Mon, Tue, Wed, etc.)
Column B = Actual date (ie. 1-Jan-18)
Column C = Hour (00:00 - 23:59)
Column D = # of calls (ie, 2 - 500)

I have that data from 01/01/18 - 06/30/19 - a total 13,104 records.

Essentially, what I want to do is pull all of the data for each day of the week by hour & break it out on individual sheets. Meaning, I want every Monday 00:00-23:59 on one, every Tuesday on another, all the way through the week - while maintaining the format of the source information above.

See attached snip from the worksheet. Any help is appreciated.

[loochy]

Originally Posted by Mr. Plow

Got a question that I'm sure a lot of you will know the answer to, but I'm needing some help. Here is what I've got.



Column A = Day of week (ie. Mon, Tue, Wed, etc.)

Column B = Actual date (ie. 1-Jan-18)

Column C = Hour (00:00 - 23:59)

Column D = # of calls (ie, 2 - 500)



I have that data from 01/01/18 - 06/30/19 - a total 13,104 records.



Essentially, what I want to do is pull all of the data for each day of the week by hour & break it out on individual sheets. Meaning, I want every Monday 00:00-23:59 on one, every Tuesday on another, all the way through the week - while maintaining the format of the source information above.



See attached snip from the worksheet. Any help is appreciated.

It seems like 7 different pivot tables inserted on new sheets would do what you want.

Or you could just use a filter and do 7 copy and pastes? This seems too simple...maybe I don't fully understand your request.