Business-card Data Into Unique Rows Per Contact?

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


ADVERTISEMENT

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 View Related

Business Day Calculation For All Rows

Aug 18, 2013

I have the following formula in a cell

=NETWORKDAYS(A2,B2)-(MOD(A2,1)>=MOD(B2,1))

its works off of cells A2 for the start date and cell B2 for the end date and works perfect

I want to use this in code to automatically count business days for all rows. I have tried the following

Dim ressfp,a4,a5
a4 = Worksheets("Master").Range("O" & b).Value 'target comp date
a5 = Worksheets("Master").Range("P" & b).Value 'actual comp date
ressfp =NETWORKDAYS(a4,a5)-(MOD(a4,1)>=MOD(a5,1))

I get an error on the word MOD saying compile error syntax error but cant work out why

i have tried replacing the networkdays formula with the following

=SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+INT(B2)-INT(A2))/7))-(MOD(A2,1)>MOD(B2,1))

but this gives the error that the curly brackets are invalid characters.

View 2 Replies View Related

Delete Multiple Rows But Not With Max Volume Of Business

May 8, 2007

I am currently doing some work which is taking ages and i wonder if there might be a quicker way of completing the work.

Each Row contains a User ID alone with the volume of business submitted under that user ID via a specific payment route, the problem i have is that i may have 10 of the same user ID's but each with a different business volume and payment route attached, I have already sub totaled by User ID so tat can see total business volume for each user however, I want to delete all the duplicate user IDs leaving only the row with the max volume of business attached.

I have approx 40,000 user ID's so i need the solution look for sets of User IDs and leave only the one with the biggest volume of business attached to it.

View 9 Replies View Related

Using VLookup On Wild Card Data

Dec 6, 2011

Suppose the data is:

beaut* 1
abc* 2

and I use the command:

Vlookup("beautiful", A1:B2, 2, false)

I want this formula to return 1. I have done it before, the other way around when the wildcard is used in the formula but not in data.

View 6 Replies View Related

Time Card Data Spreadsheet

Jul 16, 2007

How do I format a spreadsheet set up as a time card entry log so that I can enter 0543 and it appears as 05:43 AM and/or enter 1653 and it appears as 4:53 PM.

I have it formatted right now to show that data but I have to enter it as 05:43 and it appears as 5:43 AM which is what I want and the in/out times calculate with an end result of hours worked.

I want to do it without having to enter the colon.

View 11 Replies View Related

Able To Count Unique Rows Of Data

Oct 27, 2008

I want to be able to count unique rows of data.

I have three columns (A) has a date (B) has the name of a person and (C) has an audit type.

I know I need an array but not sure how to structure it for 3 columns
DateNameAudit type01/04/2008DavidCDM01/04/2008John1 in 1030/04/2008Bill1 in 1001/05/2008DavidVehicle01/05/2008DavidVehicle01/05/2008JohnCDM

The answer for these 3 columns is 5. Rows 4 and 5 are the same so are only counted once.

I can get the arrray to count unique items in the whole range and in a column but not for each row.

I have a feeling this is going to obvious but I can't work it out.

View 9 Replies View Related

Card Count: Use Sheet2 To List Each Card Number And The Number Of Times It Appears In The List

May 4, 2009

I have a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.

View 4 Replies View Related

Merging Duplicate Rows With Unique Data?

Jun 5, 2013

I have attached a before and after image of what I am looking to accomplish.

In the before image, you can see that there are 3 rows of data
- a header
- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)

In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data
- a header
- the repeating model number

What I did however in the after image, as can be seen, is I took certain data values that appeared on the repeating row (columns G - J), and included them as PRICE, COST, BEGIN, and END values in new columns on row 2.

Is there a way to accomplish this via some functions or maybe even a VBA script?

View 3 Replies View Related

Adding Unique Identifier To Rows Of Data

Jun 15, 2009

I am trying to use a formula that will populate A3 with the value of A2, all the way down the column. (as per the area highlighted in yellow) As you can see on the example sheet, the account code changes periodically, this has been causing me many problems!

As the list i am working on has around 100 differfent codes, it is too time consuming to copy paste manually

Reason for this is so that each invoice number in column B, will have a unique identifer in column A (account code)

View 12 Replies View Related

Sorting Non Unique Rows Of Data Into Another Worksheet

Jul 7, 2009

I have one worksheet which acts like a data dump (actuals) where data is just pasted in. This worksheet is sorted and edited on a continues basis. I need this data to be sorted into their respective coded worksheets (110, 120, 130).

I would like a function in Worksheet 110, 120, 130 that captures the data for that 'code'. Now the issue is that much of the data is non-unique, dates, codes, accounts may sometimes be duplicated however i still want to display the entries separately in their respective coded worksheets. (I had a solution using column numbers however it isn't viable because the data gets resorted often)....

View 14 Replies View Related

Extract Unique Data From Rows To Columns

Apr 19, 2013

I have this data

1 355
1 243
1 567
2 456
2 443
3 889
3 890
3 123

and need to sort it in columns like this

1 355 243 567
2 456 443
3 889 890 123

View 2 Replies View Related

CAGR - Add Small Trendlines Next To Some Data In Score Card?

Jul 21, 2014

I need to add small trendlines next to some data in a score card and also working out the calculating Compounded Annual Growth Rate.

1) how do i just extract the treadline, i have created the graph but don't know how to just extract the trendline

2) is there a simple calculation on excel to work out the CAGR?

View 1 Replies View Related

Compare Data Between 2 Files And Copy Unique Rows?

Jun 24, 2014

I need creating a macro which compare the values of "Column B" of attached both "Sample1" and "Sample2" excel files and if any unique value found in Column B of "Sample2" file then the entire row should be get copied in "Sample1" file after row count.

For ex. the rows colored as yellow in "Sample2" file are unique and should be get copied in "Sample1" file.

View 3 Replies View Related

Applying Data Validation Business Rules

May 4, 2012

I am in charge of validating 800+ excel reports coming from our vendors each month. I use excel data validation feature everytime I receive a new file. Data Validation feature is native to the excel workbook and cannot be saved to be used with other workbooks (at least; this is what I think).

What is the best way to write a generic validation script that I can run on any workbook. I am thinking somewhere along the lines of a test script that looks for specific data, data format, required data etc.

Here are some of the rules I have to validate against:

Cells in Column 'A' must contain data, reject if not populated.Cells in Column 'B' must contain a specific value (cat, dog, car), reject if other.Cells in Coulmn 'C' must contain (CA,AMM) only if cells in Column 'D' = 'Yes'Cells in Coulmn 'E' must be in proper casingAddress must use U.S. Postal Service standardsZip Code must be 5digits+4Exclude formatting charecters like '( )' from phone numberMinumum age must be less than or equal max ageMaximum age must be greater than Min ageCheck for populated data in cells, else reject file.Date must be in 'YYYMMDD' formatTime must be in HH:MM:SS' or 'MM:SS' formatID must contain 7 integersThis is a sample that represent 70% of files recived.

View 1 Replies View Related

Subtracting Business Hours From A Data And Time Value

Jun 26, 2008

I am trying to subtract 12 business hours from a date/time stamp. It has to be during working hours however which are 8AM - 5PM, Monday-Friday.

For example if my value is 6/23/2008 9AM

Subtracting 12 business hours would give me 6/19/2008 3PM.

View 9 Replies View Related

Moving Data From Rows To Columns - Depending On Unique Content In A Field In Column

Jun 24, 2014

I have a worksheet which contains 2 columns which is needed to work my problem.

Unique Work ID and Description

The unique work ID are the same for each description, but there are up to 5 different description associated with each unique work id.

I'm looking for an automated process but where to start to convert the 5 rows in the unique row and 5 column for the descriptions

View 8 Replies View Related

Remove All But One Non-unique Line Of Data, While Leaving Unique Data Alone

Jun 10, 2007

I am "designing" a time- tracking database. The way it works is that each user (there are multiple users) creates a new day, which is mirrored in the sheet name (i.e. if today is 06/09/07 and my name is Newuser, the sheet name is "NewusER 060907"). Each sheet is filled in, calculated based on in-sheet formulas, etc. At the end of said day, the user can " upload" the daily data from multiple days worth of data into another sheet, that the graphing macro draws from.

The one serious problem I'm having is that users can upload the daily time data for the same day multiple times, to no end. I would like to do one of two things. Either:

a. make it such that once data is uploaded it cannot be uploaded again (probably more difficult), or
b. write a loop to pull out the highest data point up the sheet and delete all other rows for that one specific day's . (probably easier)

example:

Data loaded into the sheet before the macro runs:

Row: Date:
1 06/09/07 *
2 06/10/07 *
3 06/11/07 *
4 06/09/07
5 06/10/07
6 06/12/07 *
7 06/13/07 *
8 06/09/07
9 06/11/07
10 06/14/07 *

* The rows with asterisks are the ones that I would like to have pulled to the second set of data (below)

Data in the sheet after the macro runs:

Row: Date:
1 06/09/07
2 06/10/07
3 06/11/07
4 06/12/07
5 06/13/07
6 06/14/07

Because of the constant changing nature of the information within, I would like to to make this dynamic range selection, I would prefer to avoid using Advanced Filters, if possible.

View 6 Replies View Related

Parse Contact Names

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

Inserting Contact Details From Another Sheet

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

Normalizing Contact List For Use In Excel?

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

Merging Contact Info From Different Spreadsheets To One Row?

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

How To Know Available Seats Left For Contact Center Roster

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

Outlook Macro (copying Contact And Appointment Details)

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

Import Contact Info From Macro Address Book 4.0.6

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

Creating Contact List With Dropdowns / Lookups For Multiple Offices

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

Spreadsheet To Track Customers Contact Information And Purchase History

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

Formula: Find The “best” Contact Information And Return The Result To The Merge Sheet

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

Updating Named Range When New Contact Is Added Using R1C1 Format With A Variable

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

How To Keep The First And Last Rows Of Every Unique Value

Nov 29, 2012

This is a large database. I've attached a sample.

One code is for one animal, date the animal was weighed and weight. Animals were weighed several time, hence the repetition of the code.

What I need is the first and last rows only of every code. This is in order to then calculate the weight difference and divide it by the time difference to get the daily weight gain.

The trick is to keep the first and the last rows for every code, bearing in mind there's no consistent pattern for repetition of the code (as you see, sometimes 6, 5, 3, 7). Sometimes it is even repeated twice (in this case nothing is required to be deleted).

Sample.xlsx

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved