Formula To Find Similar Data In Columns

Feb 17, 2007

I need help creating a formula to find data in one column that starts with different letters and counts them up.

For example one column would contain the words amoung others:
DPDE
DNPD
EPRO
EOTH
YBRC
YUND

In the other column I want to count up the amount of times a word starts with the letter D or E or Y. The count comes from another spreadsheet, which the CMRF Tool (See attachment) is pasted at the bottom of.

The Monthly case report file is the file I want to get the count from. These would come from Column O once the data is there. The count would end up in column D of the CMRF tool which is pasted at the bottom of the monthly case report file.

View 9 Replies


ADVERTISEMENT

Formula To Find Data And Return Other Column Value (similar To VLookup)

Jul 7, 2014

Sheet 1
Sheet 2
UPC
Sku

[Code].....

I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3

And if it doesn't find anything just return Not Found

The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above

View 3 Replies View Related

Find Similar Words Between 2 Columns & Move To Same Row

Mar 19, 2008

I have two different lists that have some similar text and some irrelevant text. What I need to do is move the matching words from column B to the same row in column a and leave blank the rows that have no similar words. Column A will not move ...

View 7 Replies View Related

Two Columns Have Data Is Not Similar Then Return The Value

Jun 13, 2007

I need a formula that looks at 2 columns and if the data is not similar to return the value.
For example
Column A
joe Bloggs

Column B
Bloggs, joe

Column C
if value in column B is not similar to column A to return value of column B

View 9 Replies View Related

Comparing 2 Columns Have Similar Data - Extract Matching Data

Apr 18, 2013

So I have this problem in excel with comparing 2 columns.

Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.

Both columns might have duplicate items because I am dealing with premium numbers.

Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.

View 2 Replies View Related

Using Find For Similar Searches

Mar 6, 2014

Problem is Findstring finds cells that contain "SS1" AND "SS1 EXT". These are separate grades.

I need Findstring to only find SS1. Using xlWhole doesn't work for me, as the cells also contain other data. For example: " SS1 48 /"

CODE:

FindString = "SS1"
Findstring1 = "SS1 EXT"

With Sheets("Sheet1").Range(Rng4)
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _

[Code] ........

View 1 Replies View Related

FIND From Right To Left Or Similar?

May 2, 2014

Cell A1 = 78-36

This is a win/loss ratio so to have it as a percentage I would have to divide the first number by the sum of the two numbers so 78/114 = 68.4% How would you do this with a formula?

I came up with this that isn't quite right, because RIGHT and FIND don't work together.

=LEFT(A1,FIND("-",A1,1)-1)/(LEFT(A1,FIND("-",A1,1)-1)+RIGHT(A1,FIND("-",A1,1)-1))
There must be a smarter way of doing this. TY

View 8 Replies View Related

Find Similar Content

Jan 17, 2007

Daily We have to search the similar numbers in a enormous database (around 35000 - 45000). Base will vary daily.

The Numbers in 2nd Row of 'wish sheet' has to be compared with 'base sheet' (B,G & L columns in Base sheeet) and the similar numbers has to be copied down from corresponding 3rd row. single character variants and double charcter varients need to be found. box is made to differentiate single and double variants. The test file may be useful to identify my problem.

It is very difficult to use find Numbers manualy. We have been helped by the experts on various circumstances in the past. So, We again seeking help from our senior brothers here.

View 9 Replies View Related

How To Find, Exist Similar Sheet Name

Apr 29, 2006

I use macros, where in DialogSheet EditBox i entered new sheet name and add it.
How to find, exist this new sheet name or not in this workbook?

View 9 Replies View Related

Combine Similar Products And Put Into Correct Columns

Jun 21, 2007

Try this on for size...

On the MSP sheet is a list of products with a weeknumber and quantity.

I need to combine products that match in the same week and add their quantities.

Then I need to put the quantities into the VOLUME worksheet.

Any products that arent listed need to be added on the bottom.

View 14 Replies View Related

VBA- Write A Macro To Find The Similar Maturity, Compute The Returns

Feb 13, 2007

I am working with financial data and am exposed to a problem that excel formula cannot solve. I am very new to VBA and would like some assistance please. I have in one excel column the list of maturities in dates eg. EN02, EN03, EN04 etc (EN=January). I have in another column the corresponding prices for these maturities. What I would like to do is compute the returns on similar maturities and paste the returns in the returns column. I can use a formula for this when the consecutive maturities are the same. The problem arises when I want excel to find the previous similar maturity, which may be 5 cells or so before. How do I write a macro to find the similar maturity, compute the returns and place that returns value in the returns column.

View 10 Replies View Related

Excel 2003 :: Multiple Columns With Similar Calculation?

Jun 27, 2014

I have Excel 2003 and use the following code for 50 consecutive columns, using each column's Row 1 as the multiplier. Each column has over 20,000 rows. It works, but I'd like to know if there's a quicker and / or neater way to do it.

Code:
Range("bo1").Copy
Range("bo2", Range("bo" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
Range("bp1").Copy
Range("bp2", Range("bp" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply

View 5 Replies View Related

Excel 2010 :: Comparison For Similar Email Addresses Separate Columns

Jun 21, 2014

Have an Excel 2010 spreadsheet with 902 email addresses in one column, and 927 email addresses in other column, sorted in alphabetical order.

Overview: Organization database has 902 Members (some have 2 email addresses subscribed to listserv) and Organizational Listserv which has all 927 (Difference is numbers are some members have 2 email addresses subscribed) BUT 902 of emails should be the same

Objective to compare Listserv Members 927 with Database Members 902. I am trying to isolate the email addresses which do NOT exist in Listserv Column, or who do not exist in Database Column for Audit purposes. All Members from the Database column should be subscribed or entered into the Listserv column.

If it's a perfect world the 902 email addresses will all be exactly the same as 902 out of the 927 Listserv email addresses.
Trying to highlight or sort and put identical email addresses on same line or using Conditionally formatting? Highlight Cell Rules/Duplicate cell rules Even when same email addresses are on the same row conditional formatting does not recognize them.

View 4 Replies View Related

Finding Duplicate And Or Similar Info Across Columns And Highlight (names And Addresses)

Jan 29, 2014

Magazine subscription list. How to highlight the customers that are already in the sheet if enter them again (renewal). Our list is like so....

ColA ColB ColC ColD ColE ColF
First Last 123 Ave City State Zip

Is there a way to highlight the row if the info on ColA, ColB, ColE, and ColF all match? Sometimes the Street info is abbreviated or entered PO Box instread of P.O. Box and they wind up on the list a second time.

View 5 Replies View Related

Formula Similar To Vlookup

Apr 10, 2009

I have created a sheet that contains a new diet program, calculated down to the precise calorie required for my training routine. Please see below for an example of one of my daily meals:

[url]

I need a formula that will help me to create a weekly shopping list (as the values in the example above will change on a regular basis). So I need to take all values from the from columns A & B, multiply them by 5 and then show me the totals in another sheet.

Similarly, I need to do the same with columns E & F, only they need to be multiplied by 2. The totals then need to be added to give me the required amount (in grams) for the week.

View 11 Replies View Related

Formula That Is Similar To The Pivot Slicer?

Jan 31, 2014

I have a list box contains of customer name and I used that to set up formula based on your selection in the list box. However, I wonder if there is a way that can select multiple customers in the list box and it will add up all that customers from your multiple selection like in the pivot slicer. I don't know if we have any formula that can do that complicated thing.

View 2 Replies View Related

Nested IF Statements Or Similar Formula

Oct 16, 2009

Trying to make it even more complicated....probably again misplaced my parenthesis

=IF((AND('Input Page'!B43="-",OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker")),'Analysis Report'!D57*'Input Page'!B32/”2”,'Analysis Report'!D57*'Input Page'!B32/"4",IF(AND('Input Page'!B43="-",OR('Input Page'!B13="Renewal In-House",'Input Page'!B13="Renewal w/Co-Broker",’Input Page’!B13=”Expansion In-House”,Input Page’!B13=”Expansion w/Co-Broker),'Analysis Report'!D57*'Input Page'!B32,'Analysis Report'!D57*'Input Page'!B32/"2"))

View 6 Replies View Related

Post Formula From Input Box - Or Similar

Jul 3, 2012

I want a user to be able to manually enter a formula into an input box which will then be used for other code elsewhere?

I have the following code but it does not want to work

HTML Code:
Dim fFormula As String
On Error Resume Next
Application.DisplayAlerts = False

[Code]....

View 7 Replies View Related

Find Phrase In One Column And Apply Formula To Different Columns Same Row?

Aug 27, 2013

I am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.

The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.

Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).

VB:
Sub Macro1()
'
' Macro1 Macro
'
Range("B10").Select
Cells.Find(What:="as % of Revenue").Activate
Range("B20").Select
ActiveCell.FormulaR1C1 = "=R[-1]C/R10C"
Range("B21").Select
End Sub

View 8 Replies View Related

Calculate Results Into Thirds Similar To Quartile Formula?

Oct 8, 2013

Just curious to know of any other formulas similar to the "quartile" function that allow you to calculate results in thirds (plus any other if known)

View 2 Replies View Related

Rows Similar Data Divided By Blank Row To Delete Data If There Are 6 Or Less

Apr 2, 2014

I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.

View 2 Replies View Related

How To Merge Similar Data

Jan 23, 2013

I have a large spreadsheet where some data are similar. for Example

Robert $1000
Louis $600
Glen $350
Doreen $2300
Robert $500
Louis $600
Glen $750
Doreen $300
Robert $3000

I want to merge all Robert together, all Louis together and show as one Robert $4500. How can I do that ? Should I use a Look up.

View 11 Replies View Related

Using Borders Where Data Is Similar

Apr 17, 2007

I have a spreadsheet that has descrptions in column D. Where the descriptions are the same ,

I need the VBA code that will do the following:

1) Sort the data by columns C (descending), G (ascending) & E (ascending)

2) Putting a border around all the data where the decriptions are the same
i.e from column A to Column I beginning from row 2

I have attached a sample file for ease of reference....

View 9 Replies View Related

Using Vlookup Or Similar Function To Find Missing Values Compared To Base Values

Apr 30, 2014

I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.

For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.

Sample:

John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional

View 1 Replies View Related

Find Last Row Of Data In Column Then Put X In Same Row In Columns

Apr 30, 2012

I have data in column C and Blank columns in D to F

I want to find the last row of data in column C then put an"x" in the same row in columns D,E and F

I am using

Range("C" & Cells.Rows.Count).End(xlUp).Select

to select the last row in column C but how do I go from there.

View 4 Replies View Related

Find The Last Row In Sheet Containing Any Data In The Columns

Jun 26, 2009

I would like to find the last row in my sheet containing any data in the columns A:O and select the cells in the range of A:O to that last row. I would then like the code to name this selected range as IMPORT.

View 9 Replies View Related

Gather Data Across Several Similar Sheets

Mar 3, 2014

This particular formula is used several times to gather various data points across 3 different sheets named Month-1, Month-2 and Month-3.I gather the data the following 3 formulas then sum it as needed with a helper cell.

=IFERROR(INDEX('Month-1'!$A$1:$G$6000,MATCH(TRUE,INDEX(1/('Month-1'!$A$1:$A$6000=$G$24)*ROW('Month-1'!$A$1:$A$6000)-MATCH($A96,'Month-1'!$B$1:$B$6000,0)>0,),0),MATCH($M$2,'Month-1'!$A$14:$G$14,0)),"")

[code]....

View 2 Replies View Related

Merging Similar Data And Amounts

Feb 22, 2013

Column A: List of document numbers
Column B: List of amounts

A B
111 $5.00
222 $10.00
111 $5.00
222 $15.00

I need to merge all equal document numbers so that it shows the full amount on that document number. It needs look like this in the end:

A B
111 $10.00
222 $25.00

I realize a pivot table will do this but the formatting of a pivot table will not work with what I am ultimately trying to do.

View 7 Replies View Related

Selecting Groups Of Similar Data

Aug 5, 2009

I have ~1,000 rows of data utilizing columns A:C. The data is "grouped" by data that is in column C. Layout is as follows for example

Row01.....Column A......Column B.......Column C
Row02.....some data.....some data.....apples
Row03.....some data.....some data.....apples
Row04.....some data.....some data.....apples
Row05.....some data.....some data.....apples
Row06.....some data.....some data.....oranges
Row07.....some data.....some data.....oranges
Row08.....some data.....some data.....oranges
Row09.....some data.....some data.....oranges
Row10.....some data.....some data.....pears
Row11.....some data.....some data.....pears
Row12.....some data.....some data.....pears

I have been trying to come up with some code that will "select" groups of data and then perform an action on those cells. As an example:

View 5 Replies View Related

Merging Cells With Similar Data

Mar 2, 2005

I am trying to merge two cells that have the exact same content in each cell. Everytime I do this, I get a pop-up warning that Im trying to merge cells with content in them and that only the upper-left content will remain. This is exactly what I want, without that pop-up, as I have to do this for multiple columns at a time.

View 2 Replies View Related







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