Clean Up Data In Contact Sheets?
Dec 2, 2013
I am putting together a master sheet of 1000s of contacts for colleagues taken from various sources (outlook contact download, linked in download, inputted business cards etc). I have noticed that everyone has their own way of using outlook and the data (normally addresses and phone numbers) appear in different columns and formats. I have used the function concatenate to add zeros back to numbers if i have had to use .csv as the download output, but I have the issue of mobile numbers in the wrong columns (i.e. someone has put a mobile number as the business phone). Is there a way of creating a new column and then applying a logic formula of some kind saying if a number starts with 07 then it appears in the new column, but if it doesnt then to ignore it therefore separating the mobile numbers so I can add to the correct column?
View 1 Replies
ADVERTISEMENT
Sep 20, 2009
I have a lot of information for various contacts, in the following format:
Name: John Smith
Address: 123 Spring Street
Phone: 555-5555
Email: example@example.com
Name: Jane Doe
Address: 456 Elm Street
Phone: 555-5555
Email: example2@example.org
Name: Richard Roe
Address: 10 Bridge Street
Phone: 444-4444
Email: example3@example.org
What I am trying to do is convert that information so that it looks in a spreadsheet as follows:
example.jpg
I feel fairly sure this is the kind of thing Excel is capable of doing, but despite much searching and experimenting can't figure out how to do.
View 4 Replies
View Related
Jul 7, 2009
Sheet1
A1Bad Data2Bad Data3Bad Data4Bad Data5Bad Data6Bad Data7Bad Data8Bad Data9Bad Data10Bad Data11Title12Good Data13Good Data14Good Data15Good Data16Good Data17Good Data18Good Data19Good Data20Good Data21Good Data22 23Bad Data24Bad Data25Bad Data26Bad Data27Bad Data28Bad Data29Bad Data30Bad Data31Bad Data32Bad Data33Bad Data34Bad Data35Bad Data
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
How can I ensure that all Rows before "Title" and all Rows after the last populated Row (denoted by the fact that there is a single blank Cell after it) are completely deleted?
View 9 Replies
View Related
Mar 17, 2009
Using VBA to clean up imported data ...
View 9 Replies
View Related
Jun 25, 2013
I am building a weekly email to my clients giving them some data on their account. When i send through file-> Share as html email, the formatting gets all screwy.
View 2 Replies
View Related
Jun 23, 2012
I need to clear the worksheet named 'ZWM0104' then have to eliminate the lines 1, 3, 5 and the columns 'A' and 'C' of the worksheet named 'Cobaia_ZWM0104' done that need to copy all cells with data from the spreadsheet called 'Cobaia_ZWM0104' to the worksheet named 'ZWM0104'
i'm try...
Code:
Sub teste()
Application.ScreenUpdating = 0
Worksheets("ZWM0104").Range("A:N").Delete
Worksheets("Cobaia_ZWM0104").Select
[Code].....
View 7 Replies
View Related
Mar 24, 2007
I need to clean out unnecessary data from a file, (see this example text file Link),
I'm not sure how to go about this in excel,
basically every file starts with 9 cells that needs to be deleted, then two cells of real data then one with garbage that needs to be deleted, it goes like that for 40 cells,
then again 10 cells of garbage that needs to be deleted, then 40 of real data and goes like that up to 3000 lines,
I know it sounds confusing but if you take a look at linked file from above it will make more sense, (to save it right click on "clean up.txt" and then save target as)
at the end I need to have all cells full of data
View 9 Replies
View Related
Feb 25, 2007
I have an excel sheet with approx 30,000 rows of data.
These are all keyword phrases.
They can be related to any subject, but for this example these are related to the root keyword phrase of "car Rent"
I have some software which basically pulls in keyword phrases from search engines and meta tags etc, including misspelled keyword phrases.
My problem;
The data is uncleaned.
In other words there might be I think it's called "Carriage return" data in there, so the row of data might be very deep (Instead of a row height of say 10.5 it could be anything, IE some could be 100 or 200 even).
There are unwanted characters, for example; ()[]{}+?!""^*
(If it could delete all unwanted characters except for letters/digits)
There is a problem I see, that if it removes _ or - between words, that it will join the words together which won't be of any use. If it deletes anything with a letter either side of it or a letter and digit, or 2 digits, 1 either side it would then need to add a space to replace the hyphen.
So for example; if there was a phrase in the list like ....
View 9 Replies
View Related
Mar 3, 2014
I am trying to use excel tools to clean dirty data and compare the two cells. The information is there but tainted with additional information that is not relevant. I have tried to use Left/Right tools to capture alpha characters leading an address number with no real success. Also, when I get the data it seems to have some embedded breaks that I can't seem to get rid of that cause my tasks to error too.
View 4 Replies
View Related
Dec 5, 2013
I would like to extract competition naps from the competition entries and then remove the string ending "nap" from the data so it can be pasted to another sheet in it's 'clean' format.
on the example sheet column B will always be the first column and the range will extend to either G,H or I (always the max range as there are always 6 to 8 races)
i would like the code to search through these columns of data and find the cell ending in 'nap' then to return this in column i, once done remove 'nap' ending from both the source cell and column i.
once done to then go through all the rows and do similar
the end result would be all naps returned into cell j and all the 'nap' endings through the data range B to I (max) to be removed to leave the horses name only.
in book 3 the first row nap was sommersturm so i have shown the outcome i would like with the nap ending in I1 removed and the horse name returned in J1 again minus 'nap' ending.
the data will always be clean with no leading/trailing/excessive spaces and always be lower case too. sometimes a space is not in between horse name and nap but it's always last 3 characters i want removed still.
View 4 Replies
View Related
Jan 6, 2009
I need to use to prepare for analysis a large set of individual participants' Excel data files collected using a psychological reaction-time experiment (a modified Stroop task). This experiment was created using a program called Superlab by Cedrus, so I posted an inquiry at the Superlab forum [url]. Their tech support suggested I might need to use macros in Excel but couldn't offer further assistance. I'll try to briefly describe (a) our Stroop task, (b) the operations I'm trying to carry out, and (c) the format of the individual Excel files,
Our Stroop task: The participant gets some instructions and practice trials, then a series of stimulus words are presented one word at a time in the center of a computer monitor in one of several colors. Word presentation order is randomized for each participant. The participant must press a key corresponding to the color of each word as quickly as possible. For each keypress (including the spacebar, pressed on instructions screens), the reaction time (in msec) is recorded in their Excel spreadsheet under the 'Reaction Time' variable. The 'Error Code' variable indicates whether the participant pressed the correct (C) or incorrect (E) color key in each trial. There are 8 types of words for a total of 48 trials. (Actually, 4 sets of emotion words and 4 sets of control-animal words, each set composed of 6 words each. The task basically measures how much peoples’ attention is captured by emotionally relevant cues in the environment.)
For each participant's data, I need an efficient way (macros?) to:
(1) clean it [i.e. discard or ignore the instruction & practice trials as well as trials where the participant was too impulsive (reaction time < 100 msec), too distracted (rt > 3000 msec), or gave the wrong color response (Error Code= E)]
(2) determine whether enough valid trials remain for further analysis (>39 trials), and if so:
(3) calculate a mean reaction time score for each of the 8 word types [where denominator for each word type is based on # remaining (non-discarded) trials for that word type]
(4) merge each participant's summarized data as single rows in a common spreadsheet (for importation and further analyses in SPSS), like this:
---------------------------
ID#...... MeanRTW1.......MeanRTW2...... (etc. for word types 3-8)
101.......1056................2013...............
102.......2148................2594...............
103...
---------------------------
Excel File Format: I've attached a sample file, and here's a simplified overview of a spreadsheet (irrelevant columns and some rows removed). First the ID number is recorded (e.g., 393E), then some unnecessary junk (exp name & date, intro, practice, instructions), then data for the 48 actual trials of interest:
-------------------------------------------
........A...............B..................C.................D............
1 393E
2 stroop.xpt
3 Wed Jan 1, 12:33:00 2008
4
5
6 Trial Name.....Trial No..... Error Code.....ReactionTime
7 introduction..... 1................C............561243
8 practice.......... 2................C............... 2062
9 red-worried........14.............C................1001 (*the first practice trial)
10 (9 more practice trial rows, deleted here)
19 instructions........3..............C................5000
20 red-chicken......19..............E............... 1205
21 blue-tense.......32...............C.................782
* (46 more actual trial rows, deleted here)
--------------------------------------------------
To clarify:
-The variable 'Trial Name' indicates nominally what stimulus was presented for each trial.
-The variable 'Trial No.', indicates the unique numerical marker for each stimulus word. (It probably should have been labeled 'Word Type'. It doesn't reflect the order of stimulus presentation. E.g., the word "tense" is always 'Trial No.' 32 for all participants, regardless of when it presents.)
-A subset of actual trial words (e.g., worried) were also used for the 10 practice trials. Thus, the 10 practice trials must first somehow be discarded or flagged to be ignored before calculating average reaction times for the remaining 48 actual trials.
View 14 Replies
View Related
Aug 22, 2007
I have an unusual request this time. Client wants this and I know it's dumb but this is what they need and I can't figure it out (not smart enough ).
They have the following as contacts in column A:
Belinda Jones & Grant Smith
Ken & Marie Johnson
Bart Simpson
They need this separated into a Contact 1 and Contact 2 so that it ends up like this:
Contact1 Contact 2
Belinda Jones & Grant Smith
Ken & Marie Johnson
Bart Simpson
This is a list of 10,000 names and I have no idea.
View 9 Replies
View Related
Oct 15, 2008
I have a workbook which I use for invoicing customers. Sheet one is the inovice template. Sheet two is the contact details of all my clients (i.e. column headings are Name, Address 1, Address 2, Town/City etc.).
In the invoice sheet I would like to be able to select the name of the client from a dropdown so that it automatically fills in the address into the other cells of the invoice.
View 3 Replies
View Related
Apr 10, 2013
I've been tasked with entering a list of contacts into our database. The list is pretty long, so I want to upload it all into our dbase by reorganizing it all into a csv file. I need fields for name, title, organization, address, phone number, etc. problem is not all contacts have info for all fields. I'm having a hell of a time getting this data into a usable format.
View 2 Replies
View Related
Jan 23, 2014
I have a spreadsheet with names and phone numbers. I have another spreadsheet with similar- but not the same names and not in the same order- with email addresses.
I have pasted all info into one spreadsheet and alphabetised it- it looks a little like this now.... (joe bloggs being an example name)
joe bloggs telephone no
joe bloggs email
Is there a way i can easily merge these two rows keeping the name the same? Otherwise it will take FOREVER!!
View 5 Replies
View Related
Feb 5, 2014
I would like to know that if i have seat count available according to floor and weekly off are planned according to team, also seat allocations is given floor wise, however i want to know how many seats are available on each floor or any given day.
I have attached excel sheet : Seat Allocation.xlsx
View 8 Replies
View Related
Feb 9, 2009
i was just wondering if anybody know of any good resourses for excel to outlook macros, specifically for copying contact and appointment details from excel to outlook. I've had a good search on the net, but it just yields lots of code, im looking more for learing resources etc.
View 3 Replies
View Related
Sep 6, 2008
At job, Mac OSX and Address Book 4.0.6 with contact info including in many cases emails. I'd like to get it of there, into Excel to manipulate, and ultimately into a FileWrecker Pro database. How to export 1087 entries from Address Book including only selective data fields is the question, and I know that's "slightly" OT, but I do want to import into Excel, so I hope that vindicates me! If not, and you can point me to a good MacForum, I'll settle.
View 9 Replies
View Related
Apr 4, 2013
I've attached my .xlsx file for easier understanding.
What I want to do is segment out a contact list by "market area" and "department" and pull the corresponding data with those labels from the Contact List tab into the main tab when validated by the dropdowns.
1.) The Market Area dropdown represents different offices.
2.) The Department dropdown represents departments within each office.
What I want the user to be able to do is to select the 2 dropdowns at the top and view an entire listing of the roster of those departments based on how they are labeled on the Contact List tab. I'm still a relative newbie to vlookup/hlookup so I've tried using them and encountered issues with it returning more than one value or being difficult to fill down the next series of values, etc. I simply want it to return the entire set of employees that fit the identifying dropdowns.
View 2 Replies
View Related
Jul 1, 2013
I am creating a spreadsheet to track customers contact information and purchase history.
A B C D
1 First Name Last Name Phone Number Etc.
2 Mike Jones
3
I wanted to know if theres a way to click on a customer (say Mike Jones) and be able to enter and view purchase history information relating to that customer in either a drop down box or different sheet?
ex.
Mike Jones
Date Item Spent Last Contacted
4/24 Toy $500 4/31
5/16 Movie $15 5/20
View 3 Replies
View Related
Feb 17, 2009
I have a formula that needs some tweaking. This formula is to reference the ACCT and find the “best” contact information and return the result to the Merge sheet. If the ACCT does not have a Parent then you reference the ACCT to the abc_Phonelist sheet. If the ACCT does have a Parent then use the Parent ACCT since it has a more desirable account number to reference against the abc_Phonelist. If the ACCT or the Parent ACCT uses Processors then the ACCT from the Processors sheet has the best reliable account number to reference against the abc_Phonelist.
In sum, there are three different possible “number tiers” that can be used. The first, the ACCT phone number. This means that the ACCT has neither a Parent nor a Processor. The second, the parent ACCT phone number. This means that the ACCT has a Parent, but neither the Parent ACCT nor the ACCT has a Processor.The third, the Processor phone number. This means that either the ACCT or the Parent ACCT has a Processor phone number.....
View 2 Replies
View Related
Feb 1, 2010
The code below is for a macro that allows the user to create a list of contacts. The column containing the names of these contacts are then referenced by a userform (code not present) by way of a Named Range. I want to update the named range whenever the user adds another contact so this new contact shows up in the user form.
I am currently referencing the range containing the names using R1C1 style, but I cannot get the variable aspect to work correctly.
View 2 Replies
View Related
Apr 28, 2006
I have a spreadsheet that has data in columns A through AE. I have built a VBA that deletes all columns except 2 (columns C & AF), and brings them over to columns A & B.
Column C contains a 10 digit number and column AF contains text similiar to "ASSIGNED ON 10 CALLS BOLDI". My VBA deletes all unneeded columns, but I am having trouble getting it to do 2 additional functions. I want it to delete the verbiage of "Assigned ON" in the cells under column AF, and delete the last number of the account number under column C, so that the account number is only 9 digits long.
Here is my VBA:
View 9 Replies
View Related
Jun 1, 2014
I want to clean up this recorded macro but not sure how, it highlights duplicate values in a column
[Code].....
View 3 Replies
View Related
Feb 3, 2010
I have created an excel tool w/ A LOT of macros in it, and the tool "speaks" to the SQL server (downloads data from it and uploads it back). My big problem now is that every time I run any of the VBA code the amount of RAM that is getting allocated to Excel (2007) is increasing - I am checking it in the Task Manager. So if I work for a long time without closing down Excel - my entire RAM is getting consumed and the system slows down, and sometimes crushes.
I was wondering if there is a way in VBA to clean up memory that is used by Excel.
Do you have any hints? I am using extensively 3 things - screenupdating off; displaymessages off, and calculations off. But this speeds up Excel but doesn't reduce the memory usage.
View 7 Replies
View Related
Feb 5, 2010
On a regular basis I have to upload cost to our order system. These cost are sent to me by the suppliers, and the part numbers have to match exactly, (from the excel sheet to the order system), or else the cost won't upload.
I have found that in some of my upload files there is a space, or sometimes several spaces, at the end of the part number. These spaces will screw up the upload. I alwyas use the "Replace" option to replace spaces with nothing, and that usually works.
In the attached sample file, there are 3 part numbers with a space at the end of the part number. I tried using the replace option. That didn't work. I also tried using the =TRIM() formula and the =CLEAN() formula, and neither of those removed the spaces. Why can't I remove the spaces from the end of these part numbers?
View 4 Replies
View Related
Sep 11, 2012
I have a bunch of "empty" cells. They only look empty visually. In fact, they contain something, because they are preventing some formulas from working. How do I cleanse them and make them truly empty without disturbing the contents of the other cells containing real data.
View 1 Replies
View Related
Mar 6, 2014
I am trying to clean this formula up. I cannot figure out if it I am supposed to use index, sumif, sumifs, vlookup, etc.
(IFERROR(VLOOKUP("100",'Total'!$9:$30,4,),0)+IFERROR(VLOOKUP("101",'Total'!$9:$30,4,),0)+IFERROR(VLOOKUP("101.1",
'Total'!$9:$30,4,),0)+IFERROR(VLOOKUP("102",'Total'!$9:$30,4,),0)+IFERROR(VLOOKUP("102.1",'Total'!$9:$30,4,),0))/1000
This formula does what I need it to do, but this is the shortest of the lines. Another cell has the same structured formula, but 15 hard keyed values. I know there is a way to clean this up, I just can't remember how and searching isn't providing the results I need.
View 9 Replies
View Related
Jan 10, 2009
Excel 2007
I am trying to clean up a template that shows #DIV/0! in the cells that contain the formula below, when there is no value present in the NamedReference.
=SUM((B6/1024)/NamedReference)
View 9 Replies
View Related
Jul 5, 2006
I'm trying to remove leading spaces in cells that have numbers. I'm familiar with the functions TRIM and CLEAN but they don't seem to work on numbers. Unfortunately, just reformatting the cell as text doesn't work.
View 9 Replies
View Related
