Comparing Character Strings In Separate Cells In Excel Spreadsheets?

Apr 1, 2014

I have a large excel spreadsheet with alpha-numeric data. I want to be able to compare two cells in different row side by side and return the difference in another cell.

e.g. I have in cell B2, "tom, rick, mike, I" and in cell C2, "mike, rick". I need to compare the cell C2 to cell B2 and return the difference in cell D2 which in this case would be the characters " tom, I". They are separated with "," and they can be in different order as you can see in the example.

View 2 Replies

Split Date / Time Strings To Separate Cells

Jul 15, 2014

splitting out date/time strings to separate cells, but I cannot find how to split the following.

I have the serviceable date of an aircraft delivery set as 'S hh:mm dd-mmm-yy', which is due to being used by an external application in this format. This is entered by the user and I need to be able to extract the date time strings and switch them to dd-mmm-yy hh:mm, excluding the S from the result as a formula in another cell.

I keep getting a #value error or returning the whole string result.

View 3 Replies View Related

Comparing Values And Returning Info From Separate Cells

Sep 18, 2013

If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).

Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
D16 (answer cell) =John
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45
D16 (answer cell) =Bob

View 2 Replies View Related

Creating Separate Reports In Separate Spreadsheets

Nov 21, 2006

I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.

I want to do this automatically.

I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).

Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.

Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet

There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...

View 9 Replies View Related

Comparing Two Different Spreadsheets

Jul 29, 2006

I have three different spreadsheet, Spreadsheet A with the name of all the clients of the company. Spreadsheet B with the name of all the clients in Toronto and Spreadsheet C with the name of all the clients in Montreal. Now i have to create a fourth spreadsheet to lookup the clients who are not in the Spreadsheet A (basically trying to find new clients). How do i search for the new clients in all the three spreadsheet?

View 2 Replies View Related

Comparing Information In Two Spreadsheets

Jul 31, 2009

I have two spreadsheets. Spreadsheet A has a column of numbers representing employee IDs (thousands of them). But I need to also have the First and Last Name for these employees to cross-reference. Spreadsheet B has the employee ID, Last Name, First Name. Spreadsheet A is large and grouped in a way that I cannot just sort by the employee ID to match up with Spreadsheet B. So basically what I am trying to do:

Look in Spreadsheet B for the employee ID found in Spreadsheet A. If found insert the information in the Last Name and First Name cell for that row copied into Spreadsheet A

Example:Spreadsheet A
Employee ID
123456
412378
<blank cell>
512345
685217
654553

Spreadsheet B
123456 Alberts Rick
654553 Johnson Mark
412378 Smith John

So I want for Spreadsheet A to compare with Spreadsheet B. If 123456 is found in Spreadsheet B, then put Alberts and Rick in the corresponding cells next to 123456 in Spreadsheet A.

View 3 Replies View Related

Comparing Two Spreadsheets With Same Data

Nov 20, 2013

I want to compare two spreadsheets with the same data. One spreadsheet is for a certain type of beneficiary and the other spreadsheet is another type. We will call them Spreadsheet A and Spreadsheet B. They both contain Zip Codes and a count of zip codes. (Some of the zip codes is not a 5 digit code, but some letters or letters and numbers.) I want to compare the two spreadsheets and see if the same zip code appears in both spreadsheets and if they do, show them in a separate tab and total the two counts from the two spreadsheets.

Column A - Zip Code
Column B - Count

View 5 Replies View Related

Comparing Strings ..

Mar 30, 2009

I have two string that I want too compare.

String 1 could be something like channel_0_user_defined._system
string 2 could be channel_0_user_defined

I want too compare string2 with string 1 at the left side of string 1.

I don't really know what I should use I found startswith, but vba doesn't have this particular function.

View 12 Replies View Related

Comparing Spreadsheets And Highlight Any Errors

Jun 11, 2009

I have 3 spreadsheets that should be exactly the same - with the same data and same positions - is there a way of comparing all 3 to see if this is true and highlight any errors - either by using a master sheet or some kind of conditional formatting

View 7 Replies View Related

FIND Method For Comparing Two Columns In 2 Spreadsheets

Dec 20, 2012

I am using "FIND" method for comparing two columns in 2 spreadsheets. So if cell value in sheet1 matches with cell value in sheet2 then copy multiple columns from sheet2 to sheet1 (similar to what VLOOKUP does but I need to return multiple values if the comparison is true).

Values that I comparing are in Column A in both the sheets. Is match I need to return values from about 20 columns (from sheet2 to sheet1). One value that I am returning is in Column B, I was able to get that working. but other values are in columns starting from M. I do not know how to get those values from Sheet2 to Sheet1.

Following is the portion of code that deals with this FIND method

Code:
Set wsTools = Worksheets("AllTools")
Set wsToolParts = Worksheets("Tools and Parts")

Set ToolPartsRange = wsToolParts.Range("A2:A" & lR)
Set AllToolRange = wsTools.Range("A2:A" & lR3)

For Each ToolPartsCells In ToolPartsRange

[Code] .....

As you may notice that the following line brings next column (B) back but I do not know how to get other column values. They are in columns from M to Z

Code:
ToolPartsCells.Offset(, 1) = ToolCells.Offset(, 1)

View 4 Replies View Related

Split Data Into Separate Spreadsheets

Sep 25, 2008

every month I import (from SAP) Local Authority staffing data for 200 schools into an Excel template. After a bit of tweaking I end up with a single spreadsheet showing formatted and adjusted data for all schools, with relevant headings separated by page breaks.

This is fine if for distributing a hard copy to schools, but I want to email it. I've seen macros that would allow data to be split onto separate tabs, but is there a way to break it down into 200 separate spreadsheets? I confess to not knowing any VBA, so be gentle with me

View 9 Replies View Related

Text To Columns - Separating Numbers From Character-strings

Feb 10, 2014

I have a field that contains rows of data, each made up of a Number followed by Text. The length of the numbers differ - sometimes 4 digits, sometimes 5 or 6. Similarly, the text differs by word-count. An example is

2546 Nags Head
75698 Dog & Duck
634 Crown

I want a way of dividing the numbers from the text (numbers in one column and text in another).

Standard 'Text to Columns' won't work: I can't use 'Fixed Width' due to the number-length varying, and I can't use 'Delimited' and [space] as it will then split all the text up word-for-word (concatenating them back will take ages as my list is 480 rows long).

View 5 Replies View Related

Break A Long Text String Into 10 Character Strings

Mar 27, 2008

I have a spreadsheet that has a column of text that is always 10 characters long. There are 10 rows of text so there could be 100 text characters if all rows are filled. The rows usually will not all be filled. There will probably be blank rows between used rows. I have been able to capture the text and put it into one cell as one long text which is ok but I want to be able to break it up into the 10 character strings again, separated with a comma and space between each 10 characters.

This is the code I used to collect the 10 character text strings and put them all together as the variable "result". I used & ", " after ... Cells(r,17) which worked fine unless there was a blank row. If the row was blank it put in a comma and space anyway so I ended up with duplicate(triplicate) commas and spaces.

Private Sub test_Click()
result = ""
For r = 5 To 32 Step 3
'If Cells(r, 17) "" Then
result = result & Cells(r, 17)
Next r
Range("r5") = result
End Sub

View 9 Replies View Related

List Days Of Each Month On Separate Spreadsheets?

Feb 22, 2012

I need to list the days of each month on separate spreadsheets...and need to to do this each year...

I was wondering if in cell A1 I type the first day of the month then is there a formula I can use to put the rest in without having to manually put them all in?

View 6 Replies View Related

Identifying Matching Rows Between 2 Separate Spreadsheets

Aug 11, 2006

I have 2 separate spreadsheets. Spreadsheet #2 is a full list of items. The item names are in Column A. In Column B is an associated primary number. In Column C is an associated secondary number. Spreadsheet #1 is a partial list of items. The item names are in Column A. The primary number is in Column B. There is no secondary number given in Spreadsheet #1.

My objective is to find out what items in Spreadsheet #1 have a secondary number in Spreadsheet #2, and to group them all together so they can be easily identified. I am attaching a couple of example spreadsheets to give an idea of what I'm working with. The example's are small so it would be easy to just eyeball them and see what ones match up however the real spreadsheets I am working from are thousands of lines long so obviously eyeballing them is not the ideal way of doing it. Is there a formula I can use to reach my objective?

View 3 Replies View Related

Separate Text Strings With Variable Lengths

Jan 29, 2014

I need to separate the text in "A" to "B" & "C", however, the length of "A" varies. Here's an example.

A
B
C

172.31.39.64 255.255.255.192
172.31.39.64
255.255.255.192

[Code] ....

View 4 Replies View Related

Splitting Data Into Separate Spreadsheets Based On Column Values

Dec 18, 2013

I have a data that has several columns. But I need to separate the spread sheet based on one particular column values.

View 14 Replies View Related

Comparing Three Lists - Identify In 3 Separate Columns

Jul 9, 2013

I am working on a project, I have three different lists that I want to compare and I want to identify in 3 separate columns which titles are found on which list.

For example:

Title List 1List 2List 3
ACAROLOGIA x
ACCIDENT ANALYSIS AND PREVENTION x
ACROCEPHALUS x
ACS CHEMICAL BIOLOGY x x x
ACS CHEMICAL NEUROSCIENCE x x x
ACS COMBINATORIAL SCIENCE x x
ACS MEDICINAL CHEMISTRY LETTERS x x
ACS SYNTHETIC BIOLOGY x x x

View 4 Replies View Related

Excel 2010 :: Comparing Cells And Displaying Text Dependent On The Results?

Aug 15, 2012

I have attached a work book example of what i am trying to do.

Column D is what i originally did in terms of the formula and now i have to have a column display text dependant on what is in column A to C.

I was trying this formula =IF(b2>a2,"Start Target Missed",IF(c2="","Failed","Tracking")) However if this isnt working.

Is there anything i can do to change this?

View 3 Replies View Related

Remove Numbers From Text And Add New Line To Separate Two Strings Based On Delimiter In A Cell

Feb 16, 2014

Actual
Result

london#123;new york#34;
london;
new york;

delhi#145;chennai#54;
delhi;
chennai;

hyderabad#748;
hyderabad;

[code]....

removing the # and numbers from text and add new line after ; symbol. So it would save me a lot of time in preparing status.

View 9 Replies View Related

Excel 2010 :: Expand Text Number Range Into Separate Cells

Feb 20, 2014

Using Excel 2010.

I have data in excel which looks like this:

Column 1 has 1200-1209,1300-1350,1523-1563
Column 2 has 1400-1409,1600-1650,1823-1863

I would like to take the range of e.g. 1200-1209 and have excel put 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 into separate adjacent cells for me. And be able to do this for each column/cell of data I have like this.

Column 1 1200
Column 2 1201
Column 3 1202

Like that only. Is it possible?How?

View 4 Replies View Related

Shade A Cell Red If Two Separate Conditions Apply (in Separate Cells)

Dec 3, 2013

I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.

View 11 Replies View Related

How To Separate Text From Numbers Into Two Separate Cells

Feb 13, 2014

I'm trying to separate text from numbers into two separate cells...

Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.

Input: Output 1: Output 2:

Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234

Ideally, I would like to do this with a formula...

View 6 Replies View Related

Add Character After 1st Character Of Cells

May 6, 2008

Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.

Original A1:A5 = {452, a-bc, 123, 4-52, abc}
Corrected C1:C5 = {4-52, a-bc, 123, 4-52, a-bc}

I can do this using an intermediate working column, but can this be done all in one formula, or via VBA?

My formulaic solution is thus: .....

View 5 Replies View Related

How To Link Excel Charts To Other Spreadsheets

Feb 6, 2014

I have to enter data into a spreadsheet daily and update a line graph with the data for 10 different production machines. I want to place the charts on their own separate excel file that way they can be easily viewed without the data next to them. I tried copying and pasting the graphs to another excel spreadsheet, but when I update the graphs on the original excel file the data does not change on the one I copied the graphs to. I use the select data range option to update the line graph data. Basically, I just want both graphs to update at the same time on both spreadsheets, but the links don't seem to update. How would I link the graphs so that both update across files?

View 2 Replies View Related

Combine Multiple Excel Spreadsheets

May 19, 2014

How can i combine multiple Excel Spreadsheets into one Spreadsheet ?

View 3 Replies View Related

Find Differences Between 2 Excel Spreadsheets?

Mar 28, 2013

I need to find the difference between 2 excel spreadsheets. They both have the same columns.

View 10 Replies View Related

Excel 2007 :: Row Size Of Both Spreadsheets Are Different

Jan 25, 2013

I am working on excel 2007. I have 2 spreadsheets opened and for some reason the maximum number of rows in one spreadsheet is = 1048576 and the maximum number of rows in the other spreadsheet is = 65536

I do not know why.

One of the spreadsheets reads compatibility mode. I have no idea what that is.

View 2 Replies View Related

SUMIF When Cells Contain Strings Found In Range Of Cells?

Jun 20, 2014

I'm looking for a function (I'm thinking 'SUMIF') that will search through column A of Sheet 1 and add column B of Sheet 1 when the cell in column A contains one of the strings listed in column A of Sheet 2. I can only find information when searching for single strings or cells.