Replace Column With Certain Criteria Matches

Apr 20, 2009

4 columns a-d. now if i find either 1 of the 2 particular strings "hello" or "bye" in column A, I want to move column b into its place and leave the rest of the columns untouched

Example:

dog, circle, blue, 123
hello, frog, green,223
pig, triangle, red, 445
bye, elephant, grey, 322

into

dog, circle, blue, 123
frog, blank, green, 223
pig, triangle, red, 445
elephant, blank, grey, 322

View 2 Replies


ADVERTISEMENT

Count If Column Heading Matches Criteria

Jul 28, 2006

I have data (coming straight out of another system, so whilst is seems messy, its the easiest way to have it in Excel).... where there are a number of rows (one for each employee) - then every column represents a date in the month (so there are about 30 columns)....the cells contain a number of hours worked by that employee for that day.

I have figured out how many hours they worked for each day of the week, but my client now wants to know:

for each employee - how many Mondays did they work?- how many Tuesdays? and so on

I am happy to figure out the day of the week for the first date in the month and then manual set up another column heading above the date that tells me what day it is - but I feel that Excel may actually be able to work out what day of the month it is - although the dates come across from the other system as text - April 26, 2006 etc...

So I would like a summary in my sheet that has column headings
Mon, Tues, Wed, Thurs, Fri, Sat, Sun
and then for each row for each employee - I would like the number of days that employee worked for the month by these Days of the Week.

I have looked at Count if - but I need to be able to select the range of cells to count, and also the range of cells to evaluate for the criteria and I cant figure it out!

View 9 Replies View Related

Return Value From 2nd Column When Value In 1st Column Matches Given Criteria

Oct 4, 2008

I have a column containing text values eg M1, T2, M3, A4 etc. and I am trying to return the value (numeric) in a second column to a cell when the text value in the first column is matched.

View 4 Replies View Related

VBA To Replace String That Matches Pattern

Nov 22, 2009

I have Userform1, Textbox1, Textbox2 and CommandButton1. Textbox1 is being manually inputted by users.

I would like that when I click Commandbutton1, that Textbox2 will be populated by whatever value was entered in Textbox1, except that the any string that matches any of the listed pattern below:

009???
017???
088???
078???
003???
663???

will be excluded. "?" here represent any 1 character.

So for example, if the user inputted "AA-90-088100ABC" in Textbox1, when CommandButton1 is clicked, Textbox2 should have the value:

"AA-90-ABC"

View 9 Replies View Related

Multiple Criteria Matches

Mar 16, 2007

I am trying to set up a worksheet whereby two critera when matched from a drop down list will populate cell 'x' with a coressponding answer. if you have Destinations then a From and To column with various locations listed below, then in a thrid column you have an output cell which is kilometres. What formula can i use to match the various location 'to' and 'from''s with the correct kilometre match?

View 8 Replies View Related

Finding Last Row In Data Row That Matches Criteria

Oct 14, 2009

have two worksheets. sheet1 has order information on it with orders, dates, customer names. sheet2 has customer name list. How can I (via vba) search through the order sheet and find the most recent order date for each customer in the customer name list. post that most recent date next to the customer name on sheet2.

View 4 Replies View Related

Userform For Search That Matches Three Criteria?

Mar 18, 2013

I created a userform for a giant spreadsheet with tons of data in it. There are three criteria. I created two command buttons - one that's "search" and one that's "close." The close works fine, but I don't know enough to program the "search" command. I'd like it to pull all data that matches the three criteria - rows H, D and M. So, if you answer all three, the macro will pull the rows of data that matches and paste it into another workbook (that opens in another window).

I'm not sure userforms are typically used for this sort of thing...

View 9 Replies View Related

Identify Matches Between 2 Lists Using Criteria

Oct 12, 2009

the attached workbook. Am trying to write a formula in Column F, which returns the results shown in Column H. Hope the workbook clearly sets out the criteria I'm trying to apply to the Lookup.

View 2 Replies View Related

Return Row Number That Matches 2 Criteria

Feb 15, 2010

I have a produced an Excel workbook which uses a VBA sign in/out userform.
Once you sign in on the Userform the sheets update. A list is completed of the times people enter and leave.

To make the code easier I currently have the name being returned to the excel sheet and performing a “match” function to return the row number. This row number is then used to carry out what I need to happen in this row. However, as you can see from attached doc (and the brief example below), based on IDnumber "2", the match function returns row 5 not row 8.
I need to have the row number returned for the IDnumber where the Out cell is blank.
This should be the last occurrance of the IDnumber

Example
Row
1 ID# In Out
2 1 9:00 13:00
3 4 9:00 13:00
4 3 9:00 13:00
5 2 9:00 13:00
6 5 9:00 13:00
7 6 9:00 13:00
8 2 14:00

View 5 Replies View Related

Populating Cell Only If Multiple Criteria Matches ...

Mar 31, 2008

I have two worksheets. The first contains a list of software (some having duplicate listings) in column A and a list of comments in column B. None of the software titles that have duplicates will have comments.

The second worksheet contains a condensed list of software (the previous list without the duplicates) and a column for the comments.

I need to have a formula that populates the second worksheet comments cells with the corresponding comments from worksheet one only if:

(1) The software title in worksheet two matches the software title in worksheet one
(2) The comment cell in worksheet one contains a comment (or text)

I have had only partial success; my obstacle being the duplicate software titles in worksheet one.

View 10 Replies View Related

Two Criteria - Multipule Matches That Are The Same - 1 Answer To Be Return

Oct 16, 2008

I've been looking through past threads and I can't find what I need to do anywhere. There was one formula that came close: =SUMPRODUCT((A2:A100="Aimbry")*(B2:B100="LW")*(C2: C100)). but this adds up all the matching results.

What I need to do is match the info in columns A and B and return a number in column C. The problem is that there are multiple rows with identical entries and I just want to return one of them. So I was thinking a version of the above that produced a mode or average would give the desired result.

View 3 Replies View Related

Count Data That Matches Criteria In 2 Columns

Feb 20, 2007

I am trying to use a "=COUNT(IF(..." formula to count the number of hourly employees that have start dates older than 3 years. Column A has the start dates and column B has the salary status (S/H). =COUNT(IF(AND(A2:A1000<DATE(2004,1,1),B2:B1000="H"),A2:A1000)) This formula returns 1 no matter what I change the data to.

View 3 Replies View Related

Multiple Matches In Array (one Or Two Criteria) And List Results

Sep 15, 2014

I have used INDEX/MATCH/ROW/SEARCH functions, in different permutations, but I am unable to get the result. The data set is something similar to the below:

Car
Region
Own
Use
Color

Honda
North
Yes
I use it to go for work
Green

[Code] .....

I want to be able to do following (2 separate tasks):

Task 1 (if in A1 on a new sheet, I had Use, i want to list all the items in an adjacent column, skipping the blank rows)):

Use

I use it to go for work

Family trips
Weekend fun
2nd car
Work

Task 2 (if i had Honda (A2) and North (B2), I want to have the colors listed in Column 3):

Car
Region
Color

Honda
North
Green

White

I know this can be done by an auto-filtering or manual sort, but I have work with thousands of similar data on a regular basis, and i want to find a formula that will allow me to list the items based on different criterion.

View 2 Replies View Related

If Match Then SUM? - Wanting To Add Multiple Values If Criteria Matches

Mar 26, 2009

Column A would be Recipe Numbers, Column B the Planned Total.

I want to search column A for all matching recipe numbers.
Then, any recipes that match, look in column B for the planned totals, adding all incidents where they match.

Then the most difficult part, which just occured to me now.
I only need the Total Planned Total and Recipe name reported once.

Recipe#..........Planned Total
450................100
600................75
620................125
450................50
620................180
450................100
600................200
450................110

I will not be able to sort these lists.

If-Match-Sum? to display:
Recipe#............Total Planned Total
450....................360
600....................275
620....................305

I've researched for the first part... and it seems like Match will only find the first match and not look further? Am I wrong on this?
How to display the results hadn't even occured to me yet, since I was still trying to figure out how to GET the result.

View 6 Replies View Related

Finding Text That Matches Format Criteria In String?

Oct 3, 2011

I'm trying to restructure a list of files at work into a format that makes some sort of sense and can be filtered into useful information.

Part of what I need to do, is match up all the drawing numbers that a certain project references. The numbers are formatted like "####A##" ex. 1234A01,1234A02,5678A01, etc. The cells that I need to extract drawing numbers from contain some description or other text (not just the drawing number) so I need to extract the drawing number from that cells value. (Ex. I need to get the drawing number 1234A01 from a cell whose value is "blah blah 1234A01 blah blah blah")

I was thinking I could just search the string for "####A##" but I'm not really sure how....I tried instr, but I think its searching for the literal value of "####A##" rather than treating the # characters as wildcards...

I tried:

Code:
if rngNames.Cells(intx, 2).Value Like ("*####A##*") then
set intStart = instr(1,rngnames.Cells(intx,2).value,"####A##")
'....
end if
but intStart remains 0, so this method is not working...

View 3 Replies View Related

Forcing Advanced Filter To Cancel Criteria If Found No Matches.

Nov 25, 2009

I have a advanced filter that works pretty much close to how I want it to. However I would like it to either cancel the new filter or copy everything if it finds 0 matches.

If the copied location is blank it breaks my sheet. So I need to find a way for it to never be blank, either by canceling it, copying everything, or finding some other way I haven't thought of.

Also for some reason my Advanced Filter does NOT Ignore blank "OR" cells. If I place a word in the top cell, then leave the bottom blank, it searches for the top cell or anything and I end up with everything. Its quite frustrating.

View 3 Replies View Related

How To Count A Column If It Matches Data From Another Column In Seperate Rows

Feb 6, 2009

I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.

Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.

View 14 Replies View Related

Lookup Up/Match: Return A Value In Column A If My Data Matches Column B?

Jun 5, 2009

I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.

View 4 Replies View Related

Macro Used Textbox In Worksheet Rather Than Cell Reference As Criteria To Find Matches

Sep 26, 2013

This is a continuation of thread 182629 [URL] ....

The End User wanted originally to type characters into cell C1 of a worksheet that the macro searched for in another worksheet, copying across every row containing the input.

The responses gave me the following code which worked perfectly:

VB:
With Sheets("PICKLIST")
myFind = "*" & .[c1].Value & "*"
End With
If myFind = "*" & "" & "*" Then Exit Sub

Unfortunately, the user NOW wants a Text Box instead of putting the characters into a cell.

I added a textbox (originally called 'TextBox2' but renamed "Choice") and "Dimed" CHOICE As Range.

Then tried the following code:

VB:
With Sheets("PICKLIST")
myFind = "*" & CHOICE & "*"
End With

But it generates a "RunTime Error 91" - Object variable or With block variable not set.

View 8 Replies View Related

Macro To Copy Data From Columns And Paste In Rows After Given Criteria Matches?

Nov 21, 2011

I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.

CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011

I get the data in the below format

CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011

I need to the macro to get the data not from the second cell.

Below is my macro

Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item

[code]....

View 2 Replies View Related

Count Unique Values In One Column If Other Column Matches?

Oct 18, 2011

How do I count unique values in Column A only if the values in Column B match?

A B
1 a
2 a
1 a
4 b
5 b
1 b

The formula would return "2" for "a", and "3" for b. Basically, there are names in column B, and I want to know how many unique things are in column A for each person. If there is a better way than a formula (pivot table?) that would be great - like a table that has each of the names (from Column B) and the number of unique items from Column A next to each unique name.

View 5 Replies View Related

Sum Of Values In Column If Text In Same Row Of Adjacent Column Matches?

Apr 10, 2014

I need to sum values in a column, but only if the text in the same row of the adjacent column meets certain criteria. Below is a simplified version as an example:

A1 - Apple
A2 - Banana
A3 - Apple
A4 - Banana

B1 - 3
B2 - 2
B3 - 1
B4 - 1

I need a formula that will add up the cells in column B that have "Apple" next to them in column A. Apple would total up to 4 and Banana would total up to 3.

View 1 Replies View Related

Delete Entire Row If Column A Matches Column D

Aug 18, 2009

Does anyone have a macro that will delete entire row if column a matches column d

View 6 Replies View Related

Sum Numbers In Column If Date In Other Column Matches

Oct 4, 2007

I have a spreadsheet which will be completed by numerous users, with a worksheet reserved for each area. The spreadsheet is to record the number of days lost to training etc on a weekly basis.

Each worksheet has 3 columns – column A DESCRIPTION, column B WEEK COMMENCING DATE and column C DAYS LOST.

The table will be completed by the manager’s as the info becomes available to them.

I will be collating the data on another worksheet and need a formula that will look in column B for all instances of 01/10/07 and then sum the corresponding cells in column C, then do the same for 08/10/07 and so on.

I have attached an example of a page.

I thought it may be VLookup or Sumif, but I don’t know how to go about it.

View 3 Replies View Related

Find And Replace With Criteria?

Jan 6, 2014

how to find and replace to prefix and suffix string/text, see this below :

JACK MANHOLD ----------- JACK
SISCA JACK HOLAND ------- JACK
ANDREW JACK ------------- JACK

View 14 Replies View Related

Replace Decimals By Criteria

Jul 14, 2006

I have multiple strings in a selection set. My goal is to replace the decimal point with null for a specific requirement. The requirement is this: "#.## "(Meaning: NUmeric.NumericNumericSpace)

Example String #1: 2.5 kips <---no change
Example String #2: 0.6 %%C <---no change
Example String #3: W1.4 <---no change
Example String #4: 0.086 SQ IN <---no change
Example String #5: 6.2.2 <---no change
Example String #6: SPC1.20 <---This one should be changed to: SPC120
Example String #7: 1.2000 <---no change

Now the real complicated string(multiple decimal points found in the same string.)
Example String # 8: 2.5 kips with 0.6 %%C using W1.4 mesh with 0.086 SQ IN. See 6.2.2 on SPC1.20 for series 1.2000. #8 should changed to: 2.5 kips with 0.6 %%C using W1.4 mesh with 0.086 SQ IN. See 6.2.2 on SPC120 for series 1.2000. <---Note:SPC120 change. SPC1.20 is the ony part of the string that meets "#.## "(Meaning: NUmeric.NumericNumericSpace)

View 6 Replies View Related

Cross Check 2 Ranges For Matches & Return Row Number Of Matches

Apr 29, 2009

I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK

View 3 Replies View Related

Return Matches & Non-Matches From Delimited Cells Against List Range

Jan 29, 2010

In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":

1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List

2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List

3.) Report In Column D: Identify and return all unique values in "Column C" as a List.

Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"

View 4 Replies View Related

Find In Column Possible Matches

Jun 13, 2007

I have a list of values in a column. In that column, I want to find 50 possible matches. Is this possible in Excel? In the column there are multiple strings of text.

After it finds these 50 potential matches, the formula should return a value next to the cell where it matched. PLEASE HELP IF THIS POSSIBLE.

View 9 Replies View Related

Multiple Criteria And Replace Cell Value Using VBA

May 1, 2013

I have some data in Column L starting from L5,
some data in Column C starting from C5
and some data Column D starting from D5.

What I need is to replace the cell values in column K with adjacent cell values in column J if the adjacent cells in column L,C and D fulfills the criteria of GBP, GBp and cell values starting with GB(for column D) respectively. I need to do this via VBA.

Initially column C
Column D
Column J
Column k
Column L

GB123
GB
1
2
GBP

[Code] ....

Desired results column C
Column D
Column J
Column K
Column L

GB123
GB
1
1
GBP

[Code] .....

View 3 Replies View Related







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