How To Create An Excel Contact Manager AND Sync With Google Contacts From Scratch + Download

hello this is randy with excel for freelancers and welcome to the contact manager with google contacts sync in this training we're going to create from scratch a brand new contact manager i'm going to show you how to upload those new contacts to google sync i'm also going to show you how to update contacts in google sync when you create them and i'm going to show you how to import existing contacts from google contacts inside excel it's going to be an amazing training i've got so much to show you including a really cool filter feature i can't wait let's get started all right thanks so much for joining i've got a comprehensive contact manager i'm going to create for you right in front of you this week we did do a contact manager a few years ago that was a very simple form and we were basically demonstrating how to turn a sheet form into database and how to create and sync that with your database this training we're going to go a lot farther i'm going to be creating a comprehensive contact manager complete with sync to your google contacts and google context back into the application i've also got a really unique and very cool filter feature i'm going to show you and a whole lot more so i hope you stick around for the whole master class it's going to be incredible if you do like these trainings all i ask is just a few things if you can click on that subscribe button below and don't forget to click the notification icons bell i create these each and every tuesday for you so i would love to bring you these every tuesday that'll get you notified when i do create these applications and training always a free download all you need to do is click the links down below either with your email or with facebook and we're going to send this application to you absolutely free right to your email or download okay fantastic if you do like these applications like i said i create one each and every week you can download them for free but if you want to get an entire pack a full zip with all the applications inside along with a one click library that's gonna allow me to share with you and click that training with youtube so you can compare one click to youtube training one click to open the application that's a hundred and seventy five i've now got 175 of those and i've got a current promotion for just 66 dollars that's the newest product if you'd like to get that that would help us out that'll keep these trainings for free each and every week so that would be great if you could pick that up i'd really appreciate that i want to get started right away on this we've got a lot to cover i'm going to try to do everything from scratch in front of you and maybe copying and pasting a little bit of code but i'm going to walk you through each step that'll help speed things up these trainings tend to be over an hour no doubt this one will be too so let's go ahead and save the first two columns for our admin there's not going to be much maybe just three rows of rabbits not too much going on i'll just color those gray and then maybe we'll do just the first three rows i'm going to give that the green those are the only ones we're going to be using for admin so i'll put a border around those and just color those green not much going on there i'll go all the way to r this is going to be our header and then what i want to do is i want to get format those cells and we'll do a fade i know you guys like to walk me to walk through you as i do each one even though sometimes they're repeats but we'll do go ahead and give it this color here and then i'll do a minimum color here medium blue like a kind of a turquoise light boot and then the row below what we'll do is the same thing as we do if you've seen my videos before if not we'll just go to the second fade this will give us that fade effect and so what i'll do is create this second and then we'll go to the light blue then we're going to create a base color it's going to be this light blue is the base color that we're going to use for the entire application down here so that's the base color i'm just going to color a whole bunch of rows all the way to again r and then just give it this base color this is the one we're focused on right here so we've got that that's going to be our basic idea now let's give it a title i want to give it a title notice this is already a merge and center going to make things a little bit easier for us so we don't have to do everything it'll speed up these trainings because they're already long and i want to give it a subheading this is a contact manager but it's going to be with google and then contacts sync now let's keep it all in capitals okay with so it's contact manager with google contact sync we're going to both go both ways on that so what i want to do is basically i want to have kind of a row of buttons here and i want and then the next i want to have some kind of a filter i want to have a list of names here so when we click and name i want the form to show up here and then some buttons to be able to save it so let's do that let's call this um context right so that'll be our context and then i want to have some kind of a filtering ability here and some buttons here so a list of contexts we're going to give this just a little bit of a deeper color so and then across here i want to be able to search the context so we'll go ahead and put in search context so because we want to have different search features so we'll search contacts and then i'll have a space so we'll put this white because that's where the user is going to be able to search and then i want to have a search by so searching by would be first name or last name so search so we can search by more than one that's going to be really cool so search by i've got a data validation here first name or last name i just put that in previously because it's going to make it go a lot faster data validation it's just a list with two options it's going to be first name or last name that'll bring it a little bit quicker okay so that's going to be pretty much it i'll put my button set across here so also this is going to be white because we want the user to enter it so anything that's uh user entered is white and now what i'll do is i'm going to select those hold down the control and select these i'm going to give these a a border format cells and the border here so we're going to give it probably a dark blue border basically like this darker blue same theme color gonna go around the outline and put the dotted line in the middle that's what we're gonna do throughout so we've got contacts and search by and then i'll also add a board around here in a bit and then a double water so then what i'm going to do is i'm going to have my fields here so perhaps something like first name would go here and then we'll put the first name field last name would go here put the last name here phone one will have multiple phone numbers phone one go here phone two would go here down below maybe i'll skip a row and go email one we'll have multiple and then i'll use a merge and center notices it's gonna be a larger field and then we'll do email two so i've just done a little bit of work to format those email too i'm going to merge and center this and what else do we want i want more than just that i want a company name so i'll put a company and then also here i want website so we got website here and then the last one probably something like notes so notice how they're already merged and centered kind of help us to go along so i'm going to hold down the control and then what i'm going to do is i'm going to i need to version center this one because that's going to be a larger field i'm going to hold down control i'm going to format those i'm going to give it the color white the background color white i'm going to right click it go to format cells then i'm going to go back to the border i'm going to use that same theme border this same color here and i'm going to give it a this line but it's going to go all the way around except i want to change the left side to be that dotted line okay and then we're going to hold down the control for the fields here and i'm going to do the same thing but this time it's going to be just opposite so i'm going to hold down all those right and i'm going to right click format those cells i'm going to go back to the border in this case we're going to do the border the top actually i'm going to go with the solid board at the top the left and the bottom the dotted lines already on the bottom merge and center this one want that email to be long merge and center and then left justify and then let's re-add the border here so we need to update that since we've just merged and centered so format the cells same border color and then top right and then bottom so we've got that there perfect and so now basically what i want is the contacts here so i'm going to also put a border around here and then so what i want to do is a list of contacts and then around those contacts i want to have when we select a specific content so i'll go all the way down here i'm just going to add a border something a little bit different so i'm going to format those cells sorry it's a little bit off the screen there i'm going to give it the double line on the right and then the single line on the left and then the bottom line and the single left here because that's going to give us a little bit so the idea is when you select a contact i want the contact list to be here and i want that contact to load now of course we have to store all that contact information i'm going to store that here on a second sheet called the contact list we're going to have a contact id unique that's going to be assigned by google all of the fields and then of course the row number we're going to be using a filtering criteria is going to help us out here and then of course we are going to have to import we needed sheet import this is going to come from google because we're going to import from google sheets we're going to bring it to google and google context and then of course we are going to all the unique names anything from google we're going to bring it in here this is going to be a temporary data this is going to allow us to import it and then we're going to bring it in here so that's the way we have it so let's take a look what do we want here i want to put an option i want the user to be able to context you know we're going to have a list of context do we want the last name first or the first name last so either one let's take a look at that so i want an option i want to give the user an option so we're going to go and give it add an option so we're going to go insert and then we're going to do a let's say check box here under the form controls and right up here i want to just put a little option here we're going to give it a name we're going to call it last name first so if this is checked it's going to be the last name last name first okay good that's the way i want it so but i also want to put this to a specific cell i want to tie this option to specific cells so format those cells we're going to take a look at that and i'm going to put it into b1 so i'm going to select here in b1 click ok so we're going to give this this as soon as we check it that's going to go to true so i'm going to ask the last name first i want to know whether the user wants to see these names either last name first or the first name first i want to know what the database rows contact database row when i select a contact i want to know exactly what row 4 5 or whatever it is i want that row to be displayed right here and the last thing is i want the selected contact row i actually have conditional formatting i was working on it i want to know the row so actually let's take a look at so as soon as i change the row i want conditional formatting i forgot to delete it but that's okay because usually i delete it so let's take a look at that conditional formatting manage rules i've already got it here it was usually supposed to delete it and then recreate it for you but you kind of get the hint so what we want to do is i want to know if b3 equals the row i want to give it this color what is that color i want to use a fill to fill this dark blue fill it's a fill effect so it's going to go from this blue to this blue right it's going to give it that fill effect and it's going to change the font and it's going to go to bold so in the font bold and that's going to be white that's going to give it so all i have to do is select on this specific content it's going to load up and i know what context is like as soon and of course i forgot the applies to manage rules it applies to c5 through c34 you can change that to 99 or whatever you want so just apply that and it'll go so now we've selected so as soon as this changes to a number you're going to see that that automatically gets highlighted so as soon as their name goes in here you're going to be able to see that okay great that's the only conditional formatting we've got on that so i wanted to cover pretty simple on the conditional formatting let's focus on the buttons also what i want up to the top here what i want is a letter you know just like one of those rolodex if you click on a letter it's only going to search names that contain a certain letter like a or a or start with them or something like that i really like that idea and so let's go ahead and do that i want to know what that would look like so i want to create letters up here and buttons i want a button set up here maybe a save and add new and a delete so that we can easily do that between contacts let's add in our buttons and then we're going to add in the filter so let's go ahead and the filter button so let's go ahead and add some shapes in there some for the buttons here so i want some button here it's going to call this save and then maybe update and then we can make that bold and then keep it white and then right justify it bold right justified and then we'll duplicate the other numbers so on the right justified that's going to be what i want and then let's give it a kind of something a little bit closer to the theme so i'm going to format it and let's give it this theme that looks good i'll make it a little bit bigger now what i'll do is i'll just duplicate that for control d it's going to duplicate that i'm going to have that for the add new so it's going to be called and i'll add some icons so that's the last thing i'll do add add new and we can shrink that and then of course delete i'm going to hold down ctrl d again it's going to be delete so we've got our buttons here and let's just shrink those up because we don't have a lot of space for them but that's to be sufficient and add new and safe update okay i like that let's just line those up holding down the control and then lining them up going to put those in the middle that's about where i want them we'll add icons but before i do that what i want to do is i want to add another type of buttons a button set up here so we select a specific letter it goes into that search content so we can search contents for that specific letter starting with a or something like that so let's do that so let's insert another type of shape and this type i want to put let's see the top row like this one rounded two rounded single corner i want to do around a double corner here so let's look at that that's rounded double corner this is the one that i want it's called rounded same side here so that's the one i want i'm going to enter that and click on it again and enter that now i'm going to keep it small but i want to put something about probably like point two and then point to a something like a width because i want each one of these for a letter and i also want to give it the same type of format that the other ones have now i'm going to put let's just say a in here okay but i want i want that a to show up obviously in the center so we'll go into the home center that like that that looks pretty good maybe bold it here okay i like the way that looks so what i want to do i'm going to call this i'm going to put this off to the side actually i'll put it right about here it's fine and then what i want to do is i want to give this a name so we'll call this sample letter now what i can do is not only do i want to name those i want to give it a value what do you mean i want to give it i want to put the b c d but i don't want to create it 26 times and i also don't want to give it a name each one has to have a unique name but i can use vba for that vba can do all that for us with just a few lines of code so that'll be great so let's do that i want to show you how to do it so basically i want to create one of these for each and i want to list them all across here so how do we do that well let's do that now inside vba we'll just create a little macro that's going to do that for us notice we have one shape called sample letter here that's all i want so let's go into the vba into the developers tab if you click on the developers you don't have that developers tab you go into file options here clone the customize ribbon and just make sure you select the developers tab that'll get you dude you can also get into it by alt f11 that's the shortcut so let's go ahead and click on the contact filters i have three modules here they're pretty much empty except a few uh subs here that we have just empty for now we'll go ahead and add those in so what i want to do is i want to create something called create letter buttons and so that's the best way to do it we can use the macro to create all those buttons for us so the first thing what we want to do when we write that is dimension something so we're going to dimension the character number we need to go through character numbers so the best way to do that is to define a character number as long what do i mean by character numbers well to get those characters right i need to know the character equivalent of each letter because i want to loop through those and in fact it's the ascii character 65 to 90. well how do i know that well if you look here on wikipedia we have the character numbers if you see this is an ascii table right here if you scroll up that's the printable characters the code we've got the octa the decimal code and the hex code here so what i'm going to be doing is i'm looking for the a now i want to get the character code i want to go from 65 this is a 65 and i want to go all the way to z so we're going to go to 90. so we need a loop to go from 65 to 90. that's just we're going to create insider so we need the character number as long and i also wanted to mention the shape name dimension the shape because we're creating different shapes so shape name as a string because we're going to update that and now we're going to focus on with sheet one so with sheet one now notice how that end with came i use auto hotkey for that that's a free software just look up auto hotkey you can create that too so with sheet one so we're going to create that loop as i talked about for character number let's see number is going to be equal to just what i had mentioned 65 to 90. that's the one we want to create so we need to close our loop next character number inside that loop what i want to do i want to set the shape name i want to set a unique name for each of those shapes each letter so shape name is going to be equal to we've already defined equals letter and what else and the character number so we need the character number what is the character number it's going to be the character number that's the one we're going to loop through okay so now we've created a shape name that's going to go from of course 65 to 90. that's going to be letter a letter you know b so each one is going to have its own one now what i want to do is i want to take our sample shape here this one right here the one we got just the right size i want to duplicate that and then i want to give it a name and a value so we can do that with the following lines of code here dot shapes sample letter dot duplicate and now when we duplicate we need to give it a name i want to give it a unique name equals the shape name we've already defined it above shape name so now that we have the shape name we can work with it we can focus on that dot shapes and then here shape name dot text text frame that's focus we want the text in there dot text range dot text so what is the text what do we want inside that well basically i want a b c or d right so what does that equal equals the character and then character number that's it that's all we have to do that sets the letter letter text but i want to do that so now that we have that now that we're setting on that what i want to do is i want to set the position i want to set the position everything but the first one in other words what i want to do is i want to once it's duplicated i want to set the position i don't want them to be on top of each other i want them to be all the way across here right so i want to set this except for the first one wherever the first one is is going to be wherever it ends after it's duplicated for example if i duplicate this ctrl d it's going to end up right here which is fine and then everything else i want to the right of that so how do we do that well the first one we don't want to worry about the left position let me just delete that here so the first one we don't need to worry about every other one we do want to focus we want to put it to the right of the previous one so how do we do that so if the shape name does not equal right not the first one letter a so it's not the first one then do something what do we want to do i want to set the left position dot shapes shape name so we're focused on that shape dot left is equal to what it's equal basically to the right of the left position of the previous one so it's going to be equals dot shapes letter right because it's going to be the ladder right but we're focused on the previous one letter and right what's the previous one and the character character number minus one right that's the previous minus one that's going to get us so now when we do dot left it's going to give us the previous one so minus 1 dot left but i want the left position of that right i want to focus on the left position right here we need this that letter and character number right one more parenthesis here double so not only do i want the left position i don't want to equal the left position i want it to be the left position plus the width of whatever the shape is right so what does that mean let me show you what that means so let's say i duplicate this shape well i want to put another shape here but i want to put that shape i want to put it right up here so it has to be the width the left of this position plus the width of this plus a little bit more because i want a little bit of a space between there so i need to do that so plus the width plus a little bit of space so what is that so the left position equals the left plus dot shapes again the same shape right the same shape but not the left position the width of this shape so let's copy that plus the width dot width of that shape that's what i want plus a little bit more right i want it on top of each other plus two that's it so there we go that's it so let's take a look at that and let's check that here that's our loop so now all we have to do is this is going to actually not only create 26 different buttons for us it can also name them accordingly so let's save our work here and let's raw we save before we run the macros in case anything goes wrong and let's just take a look let's bring this down here and see what happens when we run this if there's any issues we'll fix them as we do so let's run it and then the variable let's say shape number now it's not the correct one shape and m that's fine i'll continue on perfect look at that you see it just created it all that's exactly what i want excellent now we don't need this but i do want to create something else actually so let's format this and i want to actually increase the width of this and use this button for another purpose so let's put this to let's say 0.5 and then or maybe a little bit more 0.7 and i want to make sure i'm going to put this called a show all 0.7 that should be fine i'm going to change this to show all so let's edit the text here kind of small show all so that's going to allow us to show everything and then make it a little bit bigger all right good so now we've got it all up there and that's what we want so now we've got everything just the way we wanted to so let's use our selection tool encapsulate all of these and bring them up and then we're going to group everything together bring it up about say right here that should be fine right about there now what i want to do is i want to group them and then i want to group them all together so i'm going to line them up here but i want this one to be all be in line so let's move it over a little bit here and then put the show all here so then we're going to group them here so let's move them over we'll get out of the selection tool here move them over a little bit we have broom on the right side so we can move them over all the way on the right then i'm going to put the show all here bring it down the same great now we're ready to group them all so i'm again using my selection to align everything up so it's perfect here good now we're going to group it and now the last thing the very important thing is we're going to right click size and properties and make sure that we don't move or size of the properties moving is okay but certainly not sizing click move but don't size with the cells so now we have it so now the idea is when i click on one of these i want the a to show up in here in the search context right here and then i want to search contacts here that have a now we're ready to add in our icon so i'm going to insert some pictures here picture and i'm going to i've got some pictures saved up right here so let's just insert all of them insert and then i'll adjust the size they're all going to be different so let's just go to 0.5 and then actually we could probably use a smaller this one's going to be big this is our contact i'll use this one here right here for our logo here and then we'll make this a little bit bigger and then we'll size the rest down to something more reasonable so we'll go about point two five i think it's good point two five and then i would like to put my buttons on first and then put the icons because that way they're always going to be on top so we'll click the add new that'll be fine here delete here i've got one more button i've got to create here and then the save update and i also want to import to google so what i'm going to do is i'm going to duplicate this and then what we're going to do is we're going to call we're going to add a button up here going to call it import google contacts make that button a little bit bigger here and that's what i want to import google contacts perfect so let's check the spelling import google context save and update now take a look at this is we need to crop this because this kind of so when you want to crop it just go into the format crop because it's a little bit we can crop pictures from here too so that's no problem crop it if you know it doesn't light up because it doesn't look like the same as the rest so we'll just crop that here and then just click crop again and then return it back to its original size with you we're on 0.25 now it looks similar to the rest now we're good now of course this one we need to since we created the button after it's going to be behind the button right so we just bring it to the top that's all we need to do import google context here i'm going to hold down the control make sure everything's lined up vertically there so now we're good to go on that and then i'll bring this over here i'm going to group these buttons quickly designing these forms you'll be able to design these forms very quickly i know some of you have seen me design these forms a lot but sometimes the repetition does help because it'll allow you to be that much quicker because you've seen me do it a few times so now we've got this everything's lined up here good now we're ready to start programming it because we're all done here with the form that looks great that's just the way i want it so again we'll put in uh let's say uh charles first name one last name fredersten like that name and then give it a can do a format here and then actually we can add put an additional format you know keep in mind that sometimes google contacts want things in a certain format so keep that in mind sometimes you know certain emails and then we'll put arrows at gmail we can do multiple emails and then we can do a company name and then it's just called incorporated okay so we've got just some sample data because we're going to be first macro we're going to be doing is going to be saving this so we'll add in a website and then sample notes okay so the idea is two-fold one i want to save this information to the contact list here two i want to save it to google contacts and i want google contacts to tell me the id normally we do our own id but since we're uploading it to google we're going to have google tell us what contact id it sent to us and it's going to give us a contact i'm going to bring it right back in here so google context if you're not familiar with it is this right here this is our google context so the idea is we have google contacts and all the information when we save a contact in excel it's going to get uploaded to google context then it's going to be available on our phone and everywhere else a really cool feature likewise when we want to bring in whatever contacts we have inside our google it's going to bring them into excel so we're going to do two-fold on that and we're going to be able to filter that so let's focus on saving let's get the information into our database right here and then we'll take care of the macro and again we're going to use data mapping now i've used data mapping in the past and i'll continue using the future if you've seen it many times sorry we're going to go over one more time real quick but the idea is this right we need to get this information into this table the best way to do that when you have a lot of information is to map it so in other words f6 is going to be equal tier to f6 we're going to say h6 which is the last name going to be mapped to h6 here so the idea is to look in those cells whatever is in those cells bring them into the associated row would be the first available row for new context or for existing context it's going to be whatever row is going to be displayed right here into b2 if b2 is empty we know it is already a new contact right so we use empty we know it's a new contact if b2 had contains a value it is an existing context so let's focus on the save and update save and update this particular button is going to do two full it can either save or it's going to update our context so that's just we're going to focus on in this video so let's go ahead and add this macro and it's going to allow us to save it and it's going to allow us to put it into google so it's going to do two of those we can bring this up now we're finished with most of that i'm going to save our work and i'm going to go into the vba and we're going to focus on that mac or that first macro under contact macros this is what we're going to call save and update so this is the macro we're going to focus on here and in this particular module we've dimensioned a few types of variables that we're going to use for multiple macros contact row we're going to need i'm going to need the contact column as we loop through and the last contact row and also the field value is string that's going to help us out so how do we get this information into how do we get well the first thing what i want to do is i'm going to copy i've got some text here it's going to make things go a little bit quicker what i'm going to do is i'm just going to copy and paste some dimensions from some information so we're going to dimension oh object http this is faster than me typing it in url is a string we're going to need that response as a string because we're going to send information out to google and get things back so we need this and i'll explain what that is and the contact fields are stringing the contact id string so great so now that we got that we're going to focus on with g1 with sheet one so now with sheet1 the first thing i'll just make the first name required i want to make sure that f6 has a value if it doesn't we need to let the user know so that's the first thing we can do if dot range f6 is empty then what do we want to let the user know equals empty then message box please make sure to add a first name exits nothing we can do unless we have that okay assuming that we do have a first name we are ready to go i also want to make sure that if determine is it a new contact or is it existing b2 is going to tell us that if b2 has a value it's existing if it's empty it's new so let's write that into the code so we can use an if then else so if dot range b2 dot value equals empty then it's a new contact else existing and then close our loop and if okay so we're going to do a few things if it's a new contact that we're not doing for an existing first thing we're going to do is we need to find that row so it's going to be the contact row it's going to be equal it's on sheet 2 that's where sheet 2 and i'm going to use auto hotkey here to help us out it's going to be column a the first available in column a is going to be the last row with value plus 1. that's going to be the first available row so first available row okay so once we have that i also wanna know the row that's gonna help us out especially when we delete so i'll show you this in this row all we're gonna do is put in this formula equals rho we're gonna do that for every single new context so that's going to go down here so i want to put that into k right now because it's only one time and it's only for new context it doesn't get updated so we can do that so sheet 2 dot rank k and the contact row and the contact row dot value equals and then we'll just do it quotation marks equals and then row that's going to put that formula in right there and quotes so that's going to add row formula so now that we've added that those are the only two things that i want to do for the new context for now else it's an existing so what about for the existing for the existing what i want to do is i'm going to determine the context row contact row we know where that is right it's in b2 right here because it's not empty it contains our contact row it's going to be equal b2 it's called existing contact row so now that we have that i also want to determine the id i want to put that inside a variable contact id we have it labeled up here as a string equals where is it going to be equal it's going to be equal to in sheet 2 a right our contact id is already there because it's not a new contact and it's going to be located inside a and whatever the contact rows so let's go ahead and put that into a string variable here so sheet to dot range a and the content row dot value and that's the contact id because it's going to help us out because what if it's existing and we make an update we need to look it up on google and make the update not only in our application but in google as well pretty cool stuff so that's it so now that we have that so now what i want to do is i want to run a loop now our contact id is already here for existing so i want to run a loop from 2 all the way to 10. if you see this equals column 10. so i want to run a loop from 2 to 10. inside that loop i want to look at f6 on sheet 1 whatever is in tab 6 put it here whatever's in h6 put it here if it's a new if it's an existing it would go in whatever row it is so that's going to start out with a loop that's data mapping so we're going to do that loop so it's going to be based on the column so it's going to be 4 contact column equals 2 to 10. and then close our loop next context column okay we've already defined contact column as a long variable all the way up here context so what do we want to do inside well the first thing what i want to do is i need to get a string i need to send all of this information i need to send all this information to google so what i want to do is i want to get a big string value and i want to know the first name bill the last name smith right and i want to know put that into a string that's going to be descended to google so how do we do that well let's do this a field value field value what is the value of the field the field value is going to be equal to dot range sheet which is on sheet 1 but what is the range this the range itself is here f6 h6 j6 and so on so far so let's put that range in there sheet 2 dot cells we're using cells because both the column and the row are variables rows one in this case it's one because it's the first row and what is the column it's variable so it's going to be the contact column okay so that is the range dot value so that's where we're going to find our field value now what i want to make sure if the field just in case i don't want to send a blank sometimes when we send blanks to google it can have issues so let's do this if field value equals empty then i'm just going to add a space and let's put double quotes then field value equals i'm just going to put a space that's going to kind of help us avoid errors add space if next that way it's going to avoid some errors which i had in testing this just for blanks especially with emails so all right now that we have that so now what i want to do is i want to build this hook right this is called a web hook basically it's a string inside that string i want this called first name and then i want the first name inside this string all the last time i want everything to build up the string now in the past what we've done is we've done variables for each of them it's a bit long i've got a great shortcut i'm going to show you right now on that so let's do this we've already defined something called contact fields as a string context fields is a string so that's what we're going to use i copy that equals what's it going to equal well let's take a look at this equals whatever is existing so we're building the string we're building it up as part of the loop so in case so we're adding and adding on to it each field we're adding on so it's going to be the contact fields again because we want to know what's previously been added onto it so copy and paste that to make sure you get the variable and right it's additional and what else sheet again sheet2 let's copy this because we've already done it we'll just update that here sheet2 right and then we'll update the contact so there you go sheet2 in this case 3 right because i want to look i want to start with this name i want the value of the name of the field right i want this this is what i want first name last name that's going to start out then after i get the first name then i want the actual value okay so first the field name then the value so this is the field name right here three contact and then dot value okay so we have that sheet two row three contact code that's the value what else do i want i want some more then i want to add additional fields and i don't want the and sign because i have to separate those then i want the equal sign because what does it equal equals right and then the value so then next up is the value and where's the value located of course it's located in sheet 1 right so the value is located here again exactly the same as this one right here this is the value right here and the field value we've already defined the field value field value so that's it so we've got the value and what else after that what we need is we need to and sign so we can separate those so we also want to put that in because that's going to separate between the fields so and right and the and sign and sign i want to build in that string so that separates each field and then the last and sign the last one we just have to remove so as that builds up so that's it so next up what i want to do is actually have to add the data to the database so sheet2.cells contact row comma contact column dot value equals what's it going to equal again it's going to equal this field right here equals the value right here so we just need to copy and paste this that's going on sheet1 so that's going to add the data add data to table called database and so now we've added the data to the database we've built our string which we're going to be able to send to google in order to add the information to google and we're good to go that's it that closes our loop so that does everything so now we've built up our string so the last thing is remember we have this last and i want to remove the last one because we don't need the last one but we need everything in the middle so let's do that contact fields equals and we're going to use we use the left actually so left i want the left and then the contact that's our string contact fields is our string and i want the length because i want to remove the last character so you can use the length command and then again what about the length of what the contact fields the one just the last row fields so the length of this minus one so what that's going to remove the last character and the last character is this and sign because i want to remove that from the string great so now we've got the string built up and we've removed the last hand now we're ready to automatically send this information to google so let's do that so i'm going to copy again this space this is going to be a little bit longer line so it's easier in case i set the object http i just copy that create object ms xml that's going to send something to the server via http i need to send to google so this is going to help us do that and also what i want to do is i want to add it to google we're going to do two things we're either going to update google or we're going to add to google and that's going to be based on b2 if b2 is empty we know we're adding to it so let's start with that if dot range b2 dot value equals empty then add to google else update google right contacts just to say context and if great so perfect now we've got that now in order to send it to google we need to define the url so that url is going to be equal to and then what we want to do is i want to put a web hook in here just put in something i called hook and i'll show you how to get that in a minute we'll replace that web hook and then the question mark and what do i want i want to add the contact fields contact fields that's what else i want to add what if it's not if it's not right if it's if it's an existing what i want to do i want to send something else i want to send the url the hook we're going to replace this in a second with a different hook and but i also want to send the contact id first that's important so i want to send the contact id so we're going to put in because i need to look up the existing contact using the contact id because it's an existing one so contact id and then and what and the and sign let's add in the and sign okay and then the contact field so that's it so once i not only so that i can look it up and then add the fields to google so and contact fields so we're putting that separating that inside and we need to and here too so we've got that so basically now all we need to do is get in our hooks and we'll get those in a minute but let's fill out some of the rest of this and so once we have our hooks i'm going to again i'm going to copy and paste some information it helps us go a little bit quicker and then i'll explain it so let's i'm going to copy this from some notes i have then i'm going to put it in here so basically what we have to do is object see it's quicker open we're going to do is we're going to patch or get or anything like that we're going to post patch send it to google post or password work send it to google we're going to request a specific header content type application json so basically we're sending information to google we're going to use android grammat for that if there's no internet on air go i'm going to pause this out just in case there's errors i want to see those errors in case i have any and then what i want to go get the response what is the response back and get that into a string variable here great so basically we're going to use integramat which is a great tool and that's going to get us our urls and hooks both for updating adding to google here add to google and updating google content so we're going to use two different scenarios and i'll show you what that is so let's go into that now and take a look at that inside our tab here and then i've got scenarios this is called integramatte.com it's a really cool if you haven't seen it before it's like zapier but a lot better so we can create lots of different scenarios here we're going to be creating a scenario so basically what i want to do is i want to create something called a web hook and i want to send that web hook to google contacts and then i want to send some information back letting us know that the contact has been added we'll just focus on adding and then we're going to focus update so i'm going to click on create new scenarios once you get signed up it's free i do have the paid version because i use it a lot but you're welcome to just use the free version allows up to two or maybe three different scenarios but it's worthwhile okay so what i wanna do is i'm gonna search for some things what kind of services do you wanna integrate so i wanna use a web hook so i'm gonna type in webhook that's the first thing i wanna use and then also google contacts google contacts so that's google contacts so those are the two i want to use then we're going to continue now we're going to build it up first thing i want to do is get a web hook i want to get a custom remote we talked about getting that it's going to be called custom web hooks so what it's going to do is going to sign us a web book so the first thing we're going to do is click add and let's just call this add contact to google okay that's fine anything is work you can put in anything we don't have any ip restrictions that's fine click save what that's going to do is going to assign us this web hook right here so we're going to copy that address and this is the one we're going to use for add and what i'm going to do is i'm going to paste that inside our code right here remember hook get rid of the word hook we don't need that actually it's this one where the adding remember we're focused on adding to google this is our webinar just like sending the contact fields directly to google then what i want is the response i'm going to ask for that remember google contacts is going to sign us an id i want to know that contact id so i'm going to put that in the response so that's just what we're going to do so the first thing what i want to do is i want to run this and get all that information into that so i want to run it here let's take a look at that we've got to go back to the one we add to google this is the one we want so we've got our web hook now and we're going to what i want to do is i want to click redetermine the data structure i want to add some information in here so let's do that right now i'm going to let's click on here it's going to wait it's listening for this so we're going to take this hook remember this hook is going to end here's like it's going to end in 2 wan so make sure that that's the correct if you have multiple hooks make sure you've got the right one so 2 wan is correct now what we're going to do is i'm going to run this code see if there's any issues contact field it's going to add that on contact fields is what we have that and then let's go ahead and variable not defined again spell these right contact con cont okay get the variables and then continue on again same thing incorrect variables i don't remember the variables i create variables and i always remember what i created uh run those okay that looks good let's take a look at that it got sent here let's take a look at that back in here add to google and then let's let's run it one more time just double check it kind of went out of that so i'm going to run that just one more time run it here okay good so now it's got it's successfully determined information that's what i'm looking for i'm going to click ok that's i'm just looking for this one called successfully determined clicking ok now i'm going to move this over and i'm going to add another one and i want to add google contacts and what i want to do in this case we're creating a contact so i'm going to click create contact here now what we do is we click inside the first name and you see all this information we sent remember we sent all this information notice that these exact names are the exact same labels that we have up here because these are the labels we sent everything from first name to the notes we sent all that so that's just what i want to now all we need to do is map the data inside so let's take a look at that so the first name i'm going to click on here family name we'll put that as a last name and then email we can add those we can add multiple emails so i'm going to click on here and i'm going to call this this is going to be our email one and we'll just call this display name email one click add i'm gonna do the same thing for email two so click on add and then again we'll map this to email to here i think i put that right and then we'll call this email two okay double check that click add okay so email one is email one name email took into the same thing for phone gonna click on add phone number of course is gonna be phone one and then we'll call this give it to type you can give it any type you want i'll just leave it blank for now and then click add a number and then phone two it's what we want and click add so now we've got phone we've got notes we do have a notes field so why don't we click on here clicking adding notes i'm going to show additional settings that's going to give us some information we got this is japanese for yomi we don't need that okay so we're just going to company name we do have a company name so why don't we click on here and we also have a website here so we might as well put that in here let's call this domain that's going to be our website okay great so now we have all the information that's going to send it all to google click ok and now what i want to do is i want to get some information back right i want to get a web hook but i want to get something called a response that's what we're looking for response what do i want in that response for new context i want to get that contact id and i want to get that inside so that's all i want i want to take that contact id and i want to place it i want to place that contact id right here see we've run it we've added information but we don't have the content google is going to tell us our contact id it's going to place it right inside in this case a6 so now let's check it out let's run it so there's two things we want to do first we want to run once and then when we like what we see what we're going to be doing is we're going to turn it on so let's take a look we've got everything up we also can save it and we can add a name to it so let's call this control a add to google context so now that we've added it to google context i'm going to click run once and it's going to basically wait and so i'm going to run this again but this time i just want to make sure that we have this blank we have all this information here i'm just going to remove this here because i want it added again so we've already added it so now i'm going to run this macro again here and then i'm going to go back into our link and it's going to say there's an issue here and the issue is in this case email address parameter invalid email address so we've got an invalid email address so keep that in mind let's make sure we put in the correct email address so let's take a look at here charles gmail.com that's good to put another one because it you know we put a space in there so i'm going to put it fred at gmail.com i'll fix that so that we can accept it because all we did was have a space so it gets a little bit tricky when you're adding in this case i'll fix that to make sure that you get that updated but it'll work now let's go ahead and run it one more time because it's going to look for a invalid but if you have any errors you're going to be up here it's going to tell us because we put a space right it's looking for something and you can also ignore errors too if you want to ignore errors you can but let's fix it right so now what we're going to do is we're going to run it one more time it's going to listen here and now what we're going to do again i'm going to go back into the data so it's going to remove that information run the macro one more time from here and then we're going to follow it on the interim and see where we're headed so now it looks good we've added information our response here good everything's good so that's just the way we want we've got a response but we've got a responsible we don't know what that response is right so we actually want to place that response somewhere what do i want to do i want to take that response and i want to put it inside our database so let's do that right now but we're going to get the response regardless so but i wanted to if dot range again in this case b2 dot value equals empty right that means we know it is still a new context then right i want to do something sheet2.range a and contact row dot value equals response right a response is it right a response perfect else what do i want to do else in this case it would be an existing so this is for new existing and so in this case i also want to actually put in the content row inside beach i want to do something more so now i want to fill in b2 we know we've just placed the contact id here and now what i want to do inside b2 i want to put that database row inside b2 so that's what we're going to do so in this case copy b2 right here and then place that equals context row so that's only for adds the row adds database so now what if it's an existing if it isn't existing i want to put something else if response equals we can just put the response equals updated and we'll add the response then message box contact because we're updating it right updated and then else okay let's add that else contact not found maybe it's not found if it's not found so we've got that let's update that else and the message box here okay good we got that all done and so good we're just done with this error so that's it we finished that and now what we're going to do is we do have to build out of course the update right we've built out for new but we still need to build out the update so let's do that i also want to run some macros here at the end of this of course we're going to build out like i want to show all the macros in case there's anything because basically once i add them i add a contact i want to add it to here i want to show it here but we'll focus on that macro next okay but let's do this now we've built it so now it's ready we know it's good so now we're going to turn it on i want to run it now it's on so now every contact we add we can get rid of this here every contact we add is going to automatically be added to google so let's take a look in google contacts refresh this and take a look at our list and we're going to run it completely and get that added i got oh it's not there i got to add it to the correct account notice there's multiple accounts let me add this i've got another account let's put it on excel this account is what i want let's continue that's the one i want i want to put it i've got a multiple google account so that's the one will ask you to authorize that and click allow okay perfect let's do that one more time let's take a look at that make sure everything's working good everything's the way we want in here click ok save your changes make any save your changes now we've got it out to the right account this is excel for freelancers account so that's the one i'm using for testing let's get that again so all we need to do is test it out keep that empty clear everything else out because we want to put that id in here now we're going to assign the macro so we're going to right click assign the macro then contact save update good so now we're going to click save update we're going to run that macro here it's turned on now everything's running properly we're going to refresh it let's take a look back in the database to make sure that our contact id got added in appropriately here's our contact id now came directly from google now we can go inside the right count make sure we got the right account i'm going to refresh this and we're going to see now charles got added directly because it was the right google account okay perfect glad we got showed it to you so now we're going to focus on updating what if i make a change i want to update that how do we do that so we're going to do that right now back into instagram we're going to click on scenarios make sure you've saved any changes i'm going to create a brand new scenario here and again we're going to do the same thing webhook so click on webhook right here and then also click on google contacts this time we're going to be updating context so google and then contact so google contacts same to click continue but this time we're going to be starting out with a web hook custom web hook again and we're going to click add we're going to call this update google contact not adding a new one saving that it's going to assign us a web hook we're going to copy that address to the clipboard this time we're going to add that inside the code into our update remember we focused on this one of course if it's to a new contact this time it's for updates so we need to change that hook and this hook is for the update this one the one we just copied so we're going to save that make sure we save it good so but what do we want to do is the first thing i want to do is again run this and i want to update this so now we know since there's a value in b2 right here if i make an update here i want to make sample notes and update test so now what i want to do is i want to update this so i'm going to run this which is going to but before i do that i want to make sure that we're ready to so it's waiting notices stop it's waiting for me to send it something so we can register so that's just what i'm going to do now i'm going to send it some information by running this and because b2 is has i'm going to run the macro here contact not found no problem because we ran it we don't worry about that because response is nothing but we want to make sure that google got it so we want to make sure so it says successfully determine that's exactly what we want click ok now what we want to do is click google context i'll make sure to click the right account this time click update the contact updating a contact and i'm going to remember you have multiple accounts here i don't want the are awesome i want a different account so you can also add a new one so but i think i've got multiple ones here just a different so this is a fine anything that has my name email my second email that's the one i want so i'm gonna updating based on this account here of course you can just add one yourself just click add and you can well let's do it now just so you can see it update contact and then it's gonna again it's gonna ask you to authorize a specific email account that you have again we're focused on this one so that's the one i want to click allow so that's all you can create as many as you want so now what we do is we have the contact id okay so we're going to focus now look it sent the contact id this time it sent the contact correct see this this is the contact id how do we get that because we sent the contact id right here so you see this right in here this part contact let's go up here contact id right here we first sent the contact d then we sent the contact field so now it contains that contact id we've defined it because it's an existing one right because it's existing we've defined that contact in the first row so we've now it's an existing one so now what it's going to do is going to look up the contact we need that contact date so we're going to click here then what we're going to do again we're just going to do the same thing first name and then last name we're going to do exactly the same thing again adding the emails here so we're going to add the email email 1 we'll call this doing the same thing calling email one display name click add and then of course we're gonna do the same thing again and then email two going through this and then just give it a display name email two just as we did before so gonna be very very similar add the phone numbers clicking phone numbers again phone number one and then we don't need to add a type and then i'm going to add another one called phone number two then all we're going to do is add the company and the information okay click add now again notes we don't can have the notes here and then super easy show advanced that's where we're going to get the company information company name is here so click on the company name and then also i want to click on the website here which is the domain here and then click on the website great click ok now i want a response now we know if the response i want to just add a simple text you could pretty much add anything but i'm going to click on web hooks web hook response and i'm just going to call this updated updated that's all we want remember why is that because in our code we're looking for something here if response equals updated then message box contact updated but if it's not it's going to be contact not found so if it's not this it'll be something else so if there's an error it's going to be contact not found so that's it so that's all we need to do again we're good to go on that i think we got everything we want let's click run once and now what we can do is click here we can update that now we'll click on save update it's going to do just that waiting to listen to it contact not found that's okay we're only running it once let's take a look inside here and see what happened okay again probably an email field intricate missing requirements contact id okay so didn't find it all right let's put in a label i think we need a label here so i just want to put in here it's probably good to have not just contact id but contact id equals i think that's kind of important all right let's take a look at it now let's run it again click on the run once here and then go back in here and then just click save and update and take a look inside google watch how this happens i'm just going to go ahead and update this information right let's get rid of this and we're going to add contact id we do need that label almost forgot that so now we're good we're going to close this out click back on run once and let's just test it out again go back in here save and update now let's go take a look at the operation here one one one no errors perfect let's take a look back in excel take a look here contact updated that's what we want and of course let's take a look inside our contacts and of course this is a sample notes and update test that's what i want to see inside here inside charles here let's refresh that here so it says this is the old sample notes if i click refresh it should be updated those notes clicking on here again it says sample notes and update test perfect so now we see how context can be updated awesome so now we've got context added we've got contacts updated but i want to do additional we need to of course add add new and delete so let's go ahead and focus on that so now we've got that let's go into the macro and focus on that now the add new is really simple here i just already added it in here because all we're doing is just going to clear out fields that's it super simple nothing and we're going to include b2 remember b2 and b3 i want to clear out the selected contact row on b3 i'm going to go through this so b2 and b3 we're going to clear those out of two so all i need to do is just right click here actually i'm going to group those hold down the control group them right click assign the macro clicking on the sign macro contact add new that's okay what about delete we can write delete really quick okay we've got that so let's click on the add new it's going to clear everything out that's the way i want it perfect so what about delete so we need to write the delete and then we're going to get into the filtering and then we're going to be done it's a little bit longer training but we also need contact load that's super important contact delete contact load let's do that right now let's add in the contact load here so we can set that up so it's pretty easy with sheet 1 and then of course i want to make sure that b2 is empty b2 is going to be the row without a row we can't load the context so b2 has to contain a value so if dot range b2 that value equals empty then message box please select a contact to load exit sub okay nothing we can do unless we have now with contact row is going to be of course in b2 so let's copy this that's where our contact row is going to copy this and then the contact row is equal to b2 that's the contact row now basically all we're doing is going to just loop the opposite of that so let's do that if we're going to just the opposite of what we did right up here so we're going to basically copy this and then i'm just going to remove a few rows and i'm going to reverse it and i'll show you what that means so we don't need the field value we don't need this right but what we're going to be doing instead of saving it to the database we're bringing it from the database so all we need is just one line of code here and reverse it what do i mean by that taking this part and going to be equal so it's going to be sheet1 let's copy that properly here i'm going to drag that over to the start part and then i'm going to take the equals and i'm going to bring that right in between so basically it's sheet 1 equals whatever's in sheet 2. so it's just the opposite of that range sheet1 located here our range which is the first row equals whatever's in sheet 2. that's how we load it that's all we need to do but how do we get that now what i want to do is i want to also delete the contact we'll get to that in a minute but i want to do is i want to load all the contacts we have right here so let's load them i want to bring them here so when i select on them they automatically load here and then i select i can load that contact here it's going to place that so how do we do that let's write a macro to load those contacts in from our database row now it's going to be either last name first or first name last depending upon our selection here all right but since we've got the last macro muzzle write this delete first and then we can move on so let's write that if sheet 1 dot range b2 right we need a row dot value equals empty then again then message box please select a contact to delete exit sub okay because if we don't have a row there's nothing to delete so assuming that we do contact row equals here right here so we didn't do sheet 1 because it's pretty easy so contact roger that's the contact row so now all we need to do is sheet to dot range contact row and and then parentheses call in parenthesis and contact row dot entire row dot delete that's going to delete the row delete that's going to delete then what i want to do is i want to run a macro we're going to create that macro right now called contact list show all and then i want to do contact add new right because we're deleting we need to clear out all the fields so we want to run this macro to clear out all the fields after we've deleted and you want to update the list i want to update the list right here so now that we've got that so we've got delete we've done everything we've got uh done add new saver update either way we've add new and load and delete so we're done with that so let's take a look at the contact filters because i want to create somebody we've already done create letter buttons we probably don't need that anymore but i'll keep it for you you can see it when you download this contact list show all i want to write this macro now and basically what i want to do with this macro is i want to get the list of names first name and last name or last name and first name and bring them in to this list right here so let's write that macro right now so we can get those names in and start loading them up so contact list show all is the one i'm going to be focused on here so the first thing what i want to do is sheet1 i want to clear out any exists g1.range clear out any existing context f4 right just in case there's any filter right our filter is located here in f4 so i'm going to clear that out too so f4 is going to be cleared out f4 also i want to clear out all the names in the list so it's going to be b5 because not only do i want to clear out the names i'm going to load up there and let's put this in here contact row so i've got the contact row here let's put that bar i want to put the contact row here and the context so when i click on a contact i know what database rows here four or five whatever so i'm going to put that so i also want to clear out b5 all the way through c so i'm going to do that right now so b5 through c and then we just do a larger 99 dot clear contents clear out existing contact rows and names so once we have it clear we're ready to load in so what i'm going to do is i'm going to turn off application screen updating application when we use copy and paste it's always nice to turn it off just make sure that when you turn it off you turn it on so again i'll turn it on right now and then i'll put everything else in between so i'm going to copy and paste that equals true everything is going to go all of our code is going to go inside here so that's an easy way to do that so also what i want to do basically is i want to bring in all information so i want to clear any existing data so again here all the way in q4 through t99 i'm going to clear bring everything information now let's take a look at this this is going to be using for our filter when we have a filter based on first name or last name we're going to use our contact results remember i want to bring in first name last name or last name first name so what i want to use is a formula i want to bring in all the data if it's filtered data fine it's filtered if it's not i just want to bring it let's say it's not filtered all i'm going to doing is bringing in the names and the row and i want to bring them in right here so then what i want to do is i want to actually add the name in here actually we could probably do this i'm going to change this around a little bit the way i had it i like this what i'm going to do is i'm going to bring it here and that would bring the row this is going to help us bring it in one swoop so we've got the names i've got a formula here and it's going to be based on some information here so let's bring this i'm going to show you this formula redo the formula actually it's better to redo it so equals let's do it from here equals i want to know if i should show last name first name or first name last name and i want to base it on what's here so i'm going to base it on b1 so if this is false i want to show bill smith and fred fretters if it's true clicking here i want to show smith comma bill fretters comma fred and i want to show that automatically see which some formulas then what i want to do that's why i reversed them then i want to bring it once it's done i want to bring in all this information and i'll bring it right in here and paste it right in here that's all i really want to do so let's do that now but i want to create a formula and i want to bring down that formula for however many contexts we have we bring it in so and i want that formula to reside up here this formula step here but when we're ready to use it i'm going to copy it down here so let's do this let's just write the first formula right now this is the way i like it this is the formula here so let's take a look at if q4 first name which is required equals empty then of course just show empty otherwise if the contacts b1 remember b1 equals true that means we want to show the last name then what i want to show is r4 which is the last name comma space the first name otherwise so this is last name comma first name otherwise if b 1 is false then i want to show the first name space last name so that's what i want to do now what i want to do is i want to copy this formula right up here let's just copy that and then i'm going to paste that formula right up here because i made some changes so i'm going to paste the formula now it's not going to show anything up in here but when i copy this through vba and paste it down here and paste those formulas it's going to show up exactly how i want to so when i delete those formulas copy them this is a great way if you have a big workbook and paste those formulas in it's going to show not paste values paste the formulas right in here and paste the formulas here it's going to show exactly the way we want to so that's just one of the first thing i want to do is clear everything out here and then bring it back in here because that that we don't necessarily have to do that but let's say you have a lot of data all these formulas that aren't being used so you only really need those formulas when you have data so that's what we're going to do so q4 all the way through t99 down we're just going to clear that out because we'll use vba to add in those formulas so let's do that first we're going to focus on with sheet 2 so with sheet 2 that's going to be the first one let's say dot range q4 through t and then we can use the large row not by a but dot clear content dot clear contents so we've cleared out clear existing data now what i want to do is i want to determine the last contact row the last of course we've defined that up here last contact grows long and it's going to be in column a so last contact row is going to be equal to and that's autohotkey the last contact row so i want to know the last row because we're going to bring in what in this case all i want to do is bring in all the names and bring in the rows here so that we can do with just a few lines of code so i'm going to bring them all since we're actually showing all this isn't filtered so the last contour so dot range in this case i want to do first name and last name so q4 through r in the last row so let's do they both start in the same row they both start on row four so that's gonna make it really easy for us so q four through r and the last contact row.value equals again we're going to focus here q we're going to bring it all the way so first name last name b4 through c in the last contact row equals dot range b for through c and the last contact row it's going to bring in first and last names i also want to bring in the row numbers too so we can do that too let's just copy this and then we'll make the update so in this case right we're going to be s through s s and last country is going to be equal to k and then k okay remember k is our original row column where we want the rows are and we want to bring those into s right here so that's it so now that we have those in there we can now bring it in so let's take a look but i also want to sort them you know be nice if i have them sort them maybe nice sorted because it's if it's a long list names we do want to sort them so we should do that as well so why don't we take care of that so now that we have that but i want to know what the sort range should be but i have to know whether we're sorting first names or last names so that's going to be based on b1 right here it's going to be based on this if it's true we're going to be sorting last names if it's false we're going to be storing first names so let's do that i've already defined the sort range into that so let's take a look at that sort range as a range so let's just determine that and that's going to be ourselves if sheet 1 dot range b1 hang on a second b1 dot value equals true then the sort range is going to be equal true when stored on the last name right if it's true we're going to sort on r so then the sort range is going to equal sheet to dot range r4 else we got you set i can then set the so i set the sword range nominate equals set because it's a range else set sort range equal to sheet s4 sheet 2 q4 right first name if it's false g2 dot range q4 q4 would be for false because that's going to be the first name q4 so now we set the either r4 right if it's true we're sorting by last names which is an r4 it's going to be the first last one it's going to be 2 now we're ready to run so i've set the sort range dynamically based on the first name or last name now we're ready to focus on the sort so with dot sort i'm going to focus on the sort now and the so first thing i want to do is clear the short field sort fields dot clear clear all the sort fields now what i want to do is set that key so dot sort fields we're going to set the key add we're going to set a key what's the key key is going to be equal to sort range either one of those fields it's dynamic sort on equals excel and then we're going to sort on values sort on values also i want to set the order the order is going to be equal to ascending so in this case it's going to be xla ascending and now that we understanding i want the date option data option is going to be equal to xlnormal sort excel nor sort on normal sort normal okay we're now ready to set the range what's the range dot set range range is going to be equal we got to define sheet 2 again of course because we're on with sword sheet 2 dot range all the way from q4 through s keep everything to say s and the last contact row dot apply when i apply that filter not application and then dot apply all right so that's it that's going to set our sort now once everything's sorted in the right order i always want to copy that information bring that over so now what we're ready to do we brought it over now what i want to do is bring in that formula we've sorted all of our data based on first name or last name now i'm ready to bring in that formula and bring it down so let's do that right now we'll get out of the end with out of sort so we're going to copy dot range t2 dot copy going to copy that formula and i'm going to bring it all the way down so dot range where am i going to put it t4 through t in the last contact row last contact row and we're going to paste paste formulas dot paste special paste formulas go all the way down excel paste formulas that's what we want right here formula so bring in all those formulas now once we paste it and we're going to ready the application cut copy mode equals falsification dot cut copy mode equals false it's going to get rid of the dancing ants so once we have that we're ready to bring it over now that we've have everything sorted now i want to take this here s 4 through t in the last row and bring it all the way over into b5 through c in the last row of course our starting row is five here our starting row is four here so we do need to compensate for that so let's do that right now so sheet one dot range b five that's our starting row through c and the last contact row plus one right because we need to compensate plus one dot value equals and then all we're gonna do is based on that information dot range s and then four through t and the last contact row and the last contact row okay bringing those values and that's going to bring in both the rows and the context copy over rows and contact names sorted of course which is nice so that's it now we're good end with we're finished with that and we're getting ready to close up this let's get rid of the rest of this we don't need that application screen left into save our work before running our code especially important when we have clear contents you don't want to clear anything that you didn't intend to now i want to take this and i want to assign this particular macro to that show all button here so this button here show all this one i want to assign to it so assign the macro add show all so click that click okay and i'll see if there's any issues in that perfect now we've got our names here and look it's fretters now what if i want to do but now notice i want to assign the macro to this too if i click here i want them resorted so i'm going to right click assign the macro show all contacts you all click here so now when i click last names look now it's sorting based on bill smith first names last names perfect that's just the way i want all right great we've got just a little bit more to do gonna update the filter let's add in the filter and import google content so let's take care of the rest of the filter now so basically what i want to happen is when i click on one of these i want to put i want to search for context that at least start with that so how do we do that let's take a look inside the code and let's add some code for that it's going to be similar to what we just wrote but this one we're going to call set letter filter set letter filter we have a context so i want to set that letter filter and it's going to be based on this right here this is our thing i was going to use a formula for this but i think i'm going to use vba so i'm going to clear that out right i was thinking about using a formula but basically what here's what i want to happen when a user enters something like here i might want to search any name that starts or ends with this but when they click here i want to do something that is just starts with this so we'll show i'll show you the difference it's kind of cool we'll have two different types of filters but they're both both the criteria is going to end up directly here now this first name is dynamic it's going to be they can sort by the first name or the last name right so if i want that and i know it's linked h4 is going to be first name or last name so if i link h4 notice it just changed all we need to do is link it to context h4 so anytime we make a change inside here it's going to show automatically so we can filter by either one of them simply by selecting here great so we've got that now what we want to do is i want to take all of these and i want to use one single macro to click one i don't have this i don't want to assign 26 different macros i want to use one macro to do this and then notice we have a group we'll have to update this we just created this all right let me show you how we're going to do that so the first thing what we want to do is i want to set up it's a very simple macro so this is what we're going to do the set filter letters so what i want is i want to make a change keep in mind this each button is named remember we ran out we created a macro each button has a different name this is letter a this is letter b and so forth so all i need to use is something that we call application caller and that gives us the name of the shape that actually called it so let's try that right now so what do i want to happen i want to do two things i want to place something in here and then i want to place something in here and then i want to run the filter so what do i want to place in here i want to place the at least the label actually probably not necessary to place it in here but it kind of gives kind of gives a nice idea so let's do that in other words what i mean is we don't necessarily need to place the letter in here i just need to run the filter but it be kind of cool either way is good so let's take a look at that right now so what do i want to happen well the first thing i really want to do is we're going to use this as criteria i'm going to use an advanced filter i'm going to take all this information i'm going to filter by the first name and i need to know it starts with starts with so that means it's going to be let's say it's going to be i want a if i want to start with names that start with a and then asters so that's the wild card that means anything after a on the first name i want to show so that's what i want in there so all i need to do in sheet 2 and 3 is place that so how do we do that inside the code let's do that so context set letter sheet to dot range n3 dot value equals what's it going to equal again what i want is application but i don't want the application because if i just do the application caller it's the name the name is what right i don't want that i don't want the full name right the full name of this shape is letter a i don't want this letter how do i remove the word letter and just keep a well i can use replace for that so let's wrap that in a replace and do just that so replace what are we replacing application dot collar that's the name of the shape that called it what are we we're looking for i'm looking for letter the words letter so let's look for that letter let's spell it right and then what do i want to replace that owner plays with nothing great so what that's going to do is going to place a but i don't want just a remember i want a and the asterisk and the asterisk perfect so that's what i want so now i want to assign this particular macro to everything in that group but i need to reassign this notice show all's in the group too so we're going to i don't want that so right click assign macro paste that in there good perfect so now when i click b let's take a look in here b star that's what i want maybe one more thing it'd be nice to show what we're searching for in here so i want to do that also remember we just incorrectly assigned this macro so we need to reassign this macro to just the because we show all why did that happen because this show all is in the group so now every single thing so let's also place in f4 the letter just so we know that some we can see something happen right away so i'll do that right now so all we need to do is just copy this here and then just make the update so in this case sheet 1 right in this case it's now of course we're placing it inside f4 so we need to update the range f4 is the range f4 we don't want the asterisk in this way so letter so that's what we want so now when i click here df perfect so now we can kind of see it's not really necessary but it's kind of i'll center that because why is it not necessary because our actual filter is going to run off this so now what i want to do now i've got the criteria i've got the criteria label i've got the criteria actual so now we're ready to run our advanced filter i'm going to run that advanced filter based on here okay and we want the results also to come in here just like we did last time so the results but last time we brought in all results here we only want filtered results so how do we do that well let's run a macro we're going to run that filter macro and that's just what we're going to do that so i'm going to call it contact filter and it's this same filter basically that we're going to run whether the user is actually entering a value if they want to look for any name that becomes fred or if they want to search based on a letter so it's going to be the same macro so let's do that right now inside the vba and we'll go ahead and add some code for this it's going to be called first of all again what i want to do is clear the contents clear any existing data so we have that up here already so we can in fact let's just copy this entire macro here and paste it down here and then we're going to make some updates we need to make some movies so the first thing we want to do is clear that filter so we do want to determine the last row we're not bringing all the data in so we don't need that in other words we're only bringing filtered amount of data in based on advanced filter but we do need the last contact row last time we brought in all the data here we're bringing in a filter so i want to determine the last row run an advanced filter based on the criteria and get the results right in here based on here now we're ready to run our advanced filter so let's go ahead and put that in i'm going to automate that process just a little bit can help us that's auto hotkey that helps me write that in so let's update this of course it's not updated so it's going to be at a3 that's where we're starting our advanced filter right in the column that's going to be the first column the first row including the headers it's going to go all the way to k so let's update that inside the code k and the last of course not last transaction row that's just some automated text i used to help me but this is actually going to be last contact row contact row and then of course the criteria of course is going to be here n 2 through n 3. so i'm going to update that n 2 through n 3. great so now we've got that n 2 through n 3 because that's our criteria next up our results what do we want the results to go i want the results to go here just these three columns first name last name and row so it's going to be q3 through s3 that's where our results are going to go so let's update this range here q3 through s3 good so now we've got that unique equals false in this case because no matter what the records are i want them to show up so we've got that i just need to determine the last results row i've got last results row all the way up here so i'm going to copy that that's already been dimensioned as long so last results was going to be based on the first name which is required so it's going to be last word so equals based on q all right so now we've got that now we want to make sure that there actually is values here so let's just write some code to make sure there is if last result row is less than 4 then we know there's no results for then exit sub that's fine we can go exit now just to make sure the data that would mean if there's no values here there's nothing else we can do so that would be that there's no data so assuming that there is data we can then move on so what else do we want to know i want to then see what the sort range is here again we got to set the sword range we've got that right here set the sword range so everything else is here i've got the now i've got the data in here so everything else let's just double check everything else this way setting the sword range set the story we got the last contact row but this should be last results row remember we've updated this this time we're going to use the last result row not the last contact row so we do need to update that last result row and then last result row because we're basing it on the results not the contact row so we need to update all these things based on that last contact row and then update this to last result row based all that because our results are going to be dynamic based on the filter so q through us the last result we're setting our either our sort range based on q4 or r4 based on whether it's first name or last name sorting we are then going to apply that we're going to copy again t2 we're copying that bringing our formulas down getting the last result row paste formula cut copy multiples last is overall plus one everything's going to be the same thing that's just what i want here okay we're going to save that and now what we want to do is we've got contact filter so this is what i want to run automatically when we make that change so let's go ahead and take a look now if we click here c then i want that automatically to run so let's do that right now and update that code so let's take a look here inside letter filter i want to run this macro here when i click there so basically now we want to run the macro when we click a number so we click c no nobody starts let's update that dot range obviously we got to focus we didn't set a sheet name so we're focused on clear existing data not date here okay so we need data and then basically q4 through t this is based on sheet two okay so sheet two is where we're focused on we need to update so with sheet two that's we're gonna focus all that on here so we're gonna bring all that and bring that end with all the way down here at the bottom that's what i'm going to focus on that's important to bring it all the way down here so she everything else is defined with she won we that's very important we need to define that we don't want to forget that reset check for more bugs i'll just run the filter now and wait without width okay we've got already too many end widths we want to keep it here anywhere each way is fine reset that and then run it again okay looks good that's what we want let's take a look here first name charles that's what i want good first name fred that's what i want but we need to clear out these information let's take a look here we do need to make sure she when we need to clear everything out before this notice with the results game sheet 1 we want to clear out again b4 so we want to do make sure that's on tap for that so we've done that already here let's take a look at here all i want to do is clear this out right here and bring this in here clearing that out that's important too so now see that's perfect f that's what we want so now show all perfect that's what we want nobody starts with an a b is what we want good that's it notice we cleared out this because we're clearing it out twice but that's okay what do i mean by that it didn't show up here i'm clearing it out right here f4 if we remove this this kind of update you want f4 maybe or maybe not you want it if you want that letter if you want to know what you're searching by here next up what i want to do one more type of filter i'm going to show all i want to actually put in this and make a change and in this case i want to sort out by either beginning or end or any type of name with fred so first name as far as this so how do we do that well that's going to be on change of f4 it's going to be a change event so let's go into this code here and focus just on that we're going to focus on worksheet and then not selection change but change event so it's going to be worksheet change right here if not we make a change what are we making the change to if we're making a change to f4 that's what we want to focus on f4 then we want to do something f4 is nothing then right when i add and then then what do we want to do well i want of course run that filter but first what i want to do is i want to take whatever is here and i want to paste that right in here but i want to put the asterisk on either side of it in this case in n3 so let's do that right now so what i want to do is i want in case it includes include so sheet2 dot range n 3 dot value equals and then asterisk and what else the target value and the target target dot value and again the asterisk in the quotes and the asterisk so that way any value now i've set the criteria now we can just run the filter now we'll run the filter so all we need to do again go to contact filters take a look at this is the one we want to run based on that so we're going to copy that over here paste it in here and we've got it good so that's what i want to happen here let's take a look at that so now when i search fred perfect that's what i want to happen search by and this should work by last name too as well why because freder's and fred so now we've got two notice trials that's what i want it includes the word fred perfect okay i want to do something else i want to load these contacts when i make a selection here i want to load i want to have that contact information show up here so how do we we've already written the contact load so let's do that that's going to be on selection change of c5 through c in the last row which since we're already on the sheet we can focus on that if not right selection change based on when user makes the selection change c5 all the way down c 999 but not on any cell right it has to actually contain a value and range c and the target dot row dot value does not equal empty we want to make sure then what do we want to do that i want to do a few things and if first close the loop i want to set the row i mean of course we want to set the select row based on whatever is in b3 so we want to do that that's for sure that's the first thing we want to do range b we don't need to save the sheet because we're on the sheet dot value equals the target bro one more thing what i want to do i want to set the database row where's that database row it's going to be located in b in the target row so we can do that range b 2 dot value equals range b and the target dot row dot value once we have that now we've got the selected row in here i've got the contact database row because it's going to come from here now what we can do see notice how now all i need to do is just add one macro and that's to load the contact we'll check for issues on that bugs or anything like that what macro is that well that's this macro right here contact load here copy that go ahead back into context and add it right here now when i click on here it's going to load the contact just like that beautiful so we've got contact load we've got contact delete we've got add new save update the last and final thing is to import context i want to import contacts from google so how are we going to do that i'm going to walk you step by step through that right now the first thing we need is a trigger in other words something it's got a trigger we're going to use integra met for that and we're going to use google sheets and i'm going to import that first is going to come into our contacts then it's going to go from google contacts into google sheets and then we're going to bring it in excel so let's walk through that right now so we've got our google drive here i've got our contacts here so that's what i want to do i want to bring in so for example if i create a contact here i'm going to create to contact okay just created a contact here sally smith so i'm going to save that now because i want that imported back into excel so i've got my contacts and what i want to do is i want to use intrigue map and brand new scenario we're going to save this let's close this out we're going to save it i'm going to turn it on make sure it's on activate it save it and now we're going to create a brand new one and again what i want to do is i want a brand new trigger so we're going to do a few things here what i want to do is i want to create a brand new trigger but before i do that let's just set up a google sheet just to help us to do that so i'm going to click on new i'm going to click on google sheets this is in google drive if you don't know blame spreadsheet and then we're going to call this excel contacts sync so now inside this i want the header row but i want those header rows exactly like i have it in our data exactly so what is this again i want them exactly like this so i'm just going to copy them right here copy that i'm going to paste that right in to google sheets which allows us to do that so i'm going to paste it all right and just ctrl v i'm going to paste those right in here right just like that okay so now we have our information here now we've got everything in here and we are ready to do it we've got a name on the sheet now we're ready let's go back into integramatte and now create our automation so the first thing again we're going to start with webhook we want webhook so we're going to click on that we also want google context we're going to select that google contact and actually we want google sheets i'm going to click google contact and now we want sheets by google so we can just put in sheets and click google sheets so i want all three now i'm going to continue okay the first thing we're going to start out we need to trigger something in excel has to trigger we're going to use a custom web hook this is going to be our trigger so again i'm going to add this i'm going to call this you can call it anything you want sync context and click save and that's going to give us a web hook i'm going to copy that web hook copy that now once i have it all i'm going to do is just creating a trigger on this so that's what we're going to do so now we're going to go into macro called import and now i've got an import macro i've already written it already i didn't erase it you guys want to see this all right let me go through it here i'm going to update this this is what i have i'm going to walk you through it so we're already long on this so the first thing we want to do which is here and basically what i want to do is i want to clear out all the information and i want to take everything from that google sheets i want to sync it into here then what i want to do is i'm going to bring all this information determine if it is new if or if it's existing if it's new i want to add it to the contact list database here so that's what just what i'm going to do so let's take a look back inside the code and also back inside entry mat so basically we're going to run this patch we're going to run just like we did before basically all i'm going to do in this time i'm just sending something called import request it's just some text it's going to allow us to trigger so all i want to do is really create a trigger and then once that triggers so here let's go back inside we're at the sync contacts here sync context that's what we're looking for this is the text that we copied we're going to redetermine the data structure and now what i want to do is i want to send that i'm going to send that here so i'm going to click i'm just going to actually stop it here and then just send it from here so that's going to let us ascend it and so now once we've sent it to successfully determined all we're doing is sending a trigger so click ok now what i want to do i want to add an event now i want to add and get google context but this time what i want to do is i want to list my contacts so this is what i'll do list my contacts that's what i want to do great we can set a sort order that's not important we can set the limit we can set it to 100 although i only have three but depending upon how many you have so we've got that that's all that's gonna do is gonna bring in we oh let me set the right gmail account because i got multiple accounts excel for freelancers that's the one i want that's the one where our focus on that's the account that i'm using now so that's the one i want so click that so that's all i get to pull a context now what do i want to do with that i want to take those contacts and i want to put them into google sheets so i'm going to click google sheets and what do i want to do i want to add a row to the bottom of the table and basically all because we're going to start out with a blank table so it doesn't matter so i'm going to click on here now it's going to connect to google sheets so but i want to connect to my you can click add it'll automatically connect let's call this google contact sync anything you want and click continue and it's going to connect to the right account so i want to connect to my excel for freelancers account that's the one i'm going to focus on click allow and it's going to make that connection it's going to look up the sheet so now it's going to ask me to select a specific spreadsheet so i'm going to select excel contact sync it's the only sheet i have in there and it's going to find sheet1 is the only sheet we're focused on table contains headers yes it does it's going to look up those headers it's going to find those headers now what i can do is simply map the information from google so i click on contact id contact id is going to be put in column a first name of course is going to be first name so now all i have to do is just look up the first name and that's going to be called given name and then the last name is going to be the family name so click on the family name phone id one we'll call this phone one now you wanna go down here all the way down to phone numbers right and just click value but it's a little bit different we wanna make sure that we click here it's one but we have two phone numbers so i'm gonna do the same thing here i'm gonna go down to phone here go back down to phone addresses phone numbers i'm going to click value again but this time i'm going to click here i'm going to change it to 2. 2 is the one i want to focus on here do the same thing for emails so again because there's multiple emails email addresses clicking on value here one is fine for this one but in this one we're going to focus on two back in the email address value again change this value to two two is the one we want to focus on because there's two now you can have multiple so company of course we have company that's going to be located down here we'll call that let's take a look organization name that's the organization the website also part of the organization so we'll click on domain that's and then notes of course we do have notes all the way down here that's on the contact notes right here so we've got everything filled out we're okay we don't need nothing else in there we don't want the row that doesn't matter so click ok we don't need that once we have all the data in google sheets what do i want to do i want to bring that back into i want to just again i want a response and letting us know so we've got to set the webhook response just letting us know that we've created it and then we'll just call this contact updated actually i'd like to know how many contacts you know how many so let's take a look at that let's see how many contacts got updated so if you see here total number of bundles we can put that there let me put a space and call this contacts from of course we know it's from google right so we can put that in here from google contacts that's going to let us know now keep in mind if there's duplicates of course we wouldn't update that it means if there's the same context already so good that's the web hook i want that's everything i want we can zoom out a little bit and we can give this a name let's control a and then sync google contacts now we've got to do something else so now let's bring that in to i want to bring it into excel so i'm going to click run once and i'm first going to bring it into google sheets because that's important so now it's waiting for us and now what we can do is we can reset this and then just i'm going to run this and then we'll go through the macro as we works out so i'm going to run this macro i'm going to bring all the information in here it's going to run here and let's take a look here now it's getting the information in let's take a look all it did was sent import requests that's just the trigger we take a look at that we've got some bundle one bundle two bundle three so it's got all the information sending out all the information bringing it into google sheets we take a look at that for contacts kind of information take a look inside excel look our contacts made it in with our contacts id all the information now what i need to do is i need to get this into excel so everything worked just right and we'll walk you through it so let's take a look at this it says we need at least some source data of course we've got to have that because we just cleared it out but don't worry because we're going to bring it in so once we refresh all the next step we're going to refresh that's going to bring data into excel so that bugs okay because we haven't brought it into excel that's what i'm going to show you exactly how to do it now so what i have to do is got it now i want to share this we're going to go down to there and actually we're going to call publish to the web and we need is a shared link so not the entire document just sheet1 we're going to click publish what that's going to do is give us a link and i'm going to copy that link right now copy that control c now i'm going to go back into excel and i'm going to create a data connection i want that here so what i'm going to do is i'm going to move it over here i'm going to actually i'm going to unhide a usually high days let's unhide a i'm going to select a1 so once i've got column a unhidden what i'm going to do is i'm going to go into data from web right and i'm going to paste that link that we just copied right in here and then click go once that's going to load our data so now i want to import it and where do i want to import it write in a1 should be just fine so it's going to be a1 let's click a1 that's where i want it import a1 we don't need a new worksheet clicking on properties you can see you've got some information you want to save it enable background refresh we don't need that necessarily because we've got it tied to a macro insert cells for new data delete unused data or insert entire rows for new data overwrite existing data is fine for us because we're just bringing all the information in and then fill down formulas we're going to do that automatically so we don't need that click ok and click ok and now it's going to bring all the data in just as well so it's going to hide column a for us so it's bringing it in perfect so now the information's brought in now we have all the information just as we want it brought in from google sheets you can see here we've got everything here we don't need the row it's not important here great so now i've got the information in here how do i do that inside the code that's exactly the next step once we get the response this workbook refresh is going to do just that it's going to refresh all the connections when i run that code it's exactly the same as going into data and refresh all it's going to pull anything else from google if it's found it's going to refresh everything from google now what i need to do is i need to determine which contacts are new and which are existing and we can use contact id now i created a named range called contact id if you go into formulas name manager contact id tab over we see that i've created a dynamic named range for all the contacts id so all i need to do is determine if there's a match so basically what i want to do is i want to run some kind of a filter an advanced filter i want to look at all the contacts right here's sally smith notice we've just created those inside google right and so what i want to do is i want to determine a formula if the formula exists if it exists i want to put true so there's going to be a formula and i want to take that so what i'm saying is i want to determine if it's found let's just put something like exists i'm going to create a formula here so if it exists then i want to know so how do we know if this exists inside let's create a formula right here well let's do it here equals is air check on that match if it's an error i want to know match what am i looking up i'm looking up this contact id where am i looking up and looking up in the named range contact id that i created and i want an exact match i want to know if this is an error so this is not an error this one should be an error so when i drag this down here this one's an air right these two are errors because they're not inside notice charles and frederson is not inside here okay that one contact's not in so those two contacts are not there so those are the only ones that i want to import so now that i have it all i need to do is create an advanced filter and i only want to the results to show it's true and i want those results here once i know all those ones that are true meaning they don't exist in results i can bring them over and that's just we're going to do inside the rest of the code so let's take a look at that the first thing what i do is i'll put that formula let's copy that formula i'm going to put it right in l 1. all i'm going to do within vba is copy this bring it down paste special paste the formulas so that's going to say now i want to run an advanced filter base it on whatever is true have the results here bring those results and put them right in here so that's all we need to do in the rest of the code so let me walk you through that since i already created it for you once i determine the last row i can then copy down the formula check the context so l4 through l contour formula equals l1 formula that's it let's actually make this i'm going to change this up i'm going to bring this down to row one because that's where we're starting row one so it's not really copy and paste it's a value it's a formulative formula so if we start on row one and we'll do just that so l1 formula equals four through l so that's going to bring our formula our check column formula down now we're ready to run our advanced filter notice i start to move faster and slur my words especially in a two hour video sorry about that two hours all right moving on we're almost done here b3 and l we're gonna run that advanced filter last contact row we've already defined that up here we're gonna run an advanced filter copying it we're gonna set the criteria to n2 to n3 this is what i want i want to know only n2 exists n2 and 3. we're going to take those results and place them inside p through z so that's where we don't need this actually i need this and then this exists so exists is fine actually it should be new right new right new would be false it's not new true it is near so that makes more sense new and then new so okay so that way everything's same remember we have to make sure they're the same here so it is new it's true if it's not new it's false that makes a little more sense continuing on once we run our advanced filter we have the results come to p3 z3 then what i want to determine the last results row based on p right the last results are i don't know it depends how many contexts in this case the last results row would be 5. so then i want to take from our new and bring it value to value so i want to determine the last row so let's continue on if the last results row is less than four then exit the sub that means there's no data assuming that there is data we can determine the last contact database or i need to know the first available row where are we going to put this data that we brought it over in this case it's going to be the last row of the value plus one the first row so that's where we're going to place it so we're going to get that last row using sheet 2 a and x l up then i'm going to place it in a in the last contact database row and place it there and j in the last context database row plus minus four that's where we're going to place it this should be plus one because this is going to be plus one right that's where we want to place it in this case minus three i'm gonna update that that's where we wanna place it right because this is going to give us our last row plus one the first available row last contact database row we've already added one j contact that's going to place everything and then of course the row we also want that to there so that's what we have to do so a through the last contact through j what does that mean that means a through j we're going to place all the information and it's going to come directly from here p4 all the way to y bringing that information in all the way through y p3 through z actually y we don't need z we don't need that new through y good enough so it's going to bring the last cases the notes and it's going to come from notes so through j that's just what i want bring in the last thing we want to do is i want to set the formula so we've brought in all the way to notes now we're ready to set the formula so we're ready to continue on with the formula here bringing in that row formula next up just message box i want to know how many contacts are open how many contacts got imported which is going to be the last results wrote minus three so let's take a look at that let's run that macro and see just how that works continuing on we're going to tie that macro here bring it to that button here so we can right click we've already grouped that button right click here and then assign the macro and then we're going to call this google contact sync here we've already have it right here contacts import right here so clicking ok now we just click on this run the macro it's going to let us know how many contacts got imported so let that go through all right we see that two contacts have been imported perfect but i want to get those new contacts right here so we take a look at the contact we see the brand new contacts that got all we need to do is again run our refresh so i want to run that macro so let's take a look inside what macro we want that's going to contact show all want to run that macro and that's going to be after the import so we take a look at the import and then at the bottom of this i'm just going to run that macro right there paste that in there all that's going to do is just basically going to run this macro just as we did here bringing in those brand new contacts now we can load their information in all right this has been an amazing marathon training i hope you have enjoyed this training i've shown you amazing contact manager how to create your own save add new and delete contacts how to automatically filter based on any name how to sync and import to google context and how to bring google contacts back into excel it's been an incredible training thanks so much if you like this training don't forget about the brand new 175 workbook zip file on sale now for just 66 that'll help us out a lot can't wait next week we'll see what's happening thanks so much we'll see you then

Channel: Excel For Freelancers

Views: 15,900

Rating: 4.9661732 out of 5

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Free Contact Manager, Contact Manager Free, Excel Contact Manager, Excel Contact Management, Contacts Manager, Create Excel Contact Manager, Managing Contacts, Manage Customers Excel, Excel Google Contacts Sync, Sync Google Contacts Excel, Excel Google Contacts, Update Google Contacts

Id: guqUCV31NBo

Length: 105min 54sec (6354 seconds)

Published: Tue Dec 08 2020