Matching Cell Formats Based On Matching Cell Values

Jan 28, 2010

I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.

I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.

View 9 Replies


ADVERTISEMENT

Fill Table Based On Matching Cell Values & Export

Dec 21, 2007

(refer to attached workbook)

For what I'd like to do, the attached workbook actually represents 2 or more workbooks. The 'Project-PO Report' tab is a sample of what will be several workbooks/reports that will be generated from the data in 'POTASK'.

The 'CMD_BUTTON' tab represents how I plan to use a command button " IMPORT DATA" to import the data into this workbook, then use "RUN PROJECT-PO-REPORT" to call the MACRO to achieve my objective,as stated below. (Originally,this workbook was about 8mb, so to get this under the size for attachment, I just copied the bare minimum of what I believe would be needed to assist me, and so there's no buttons or macros or dynamic name ranges, as there will be...)

Here's what I want to do: ...

View 3 Replies View Related

Bank Reconciliation - Many To One Matching And Partial Cell Matching

Nov 19, 2009

programming 2 macros in a bank reconciliation sheet I am trying to create.

Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.

The following two situations can occur and which need to be covered with a macro.

Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.

Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.

View 12 Replies View Related

Align Matching Row Values Column And Remove Non-matching Row Values?

Jan 27, 2014

see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.

Exemple.xlsx

View 6 Replies View Related

Replace Cell Contents Based On Matching Data In Another Cell (On Another Sheet)?

Apr 28, 2014

I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.

I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?

In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.

View 5 Replies View Related

How To Display All The Values Of Matching Key In One Cell In Excel

Dec 7, 2013

Am using Excell for some times and understood the basic operations like VLOOKUP and other formulas, and it was really usefull. Now i have another requirement where i need to get all the values matching from another tab.

Below is the example: I will be entering all the values in Tab 2 manually. As well as i will be entering the Categories (Column A) in Tab1. I want the Values (Column B) in Tab1 to be populated.

TAB 1CATEGORY
VALUES

AA1
1
2

AA2
5

BB1
3

BB2
4
6

TAB 2
CATEGORY
VALUES

AA1
1

AA1
2

BB1
3

BB2
4

AA2
5

BB2
6

I have 2 problems in that

1) It is not dynamic, i have to change Tab 2 frequently.
2) values are listed below the categories

View 4 Replies View Related

Return A Value Of Two Matching Cell Values From Another Sheet

Jan 7, 2010

I have two sheets - one work sheet and the second - database with existing entries. Going by visual example below, I need the column C of the work sheet to automatically populate with the data pulled from the database sheet. For example, C4 on the Work sheet should find both values "Bike" and "Green" in one row on Database sheet and return the corresponding value 44444 from F97. The work sheet cells in the row C should remain empty if no match found on the database sheet.

Sheet 1 (Work sheet)
-------A--------B---------C
1---Auto-----Red
2---Auto----Green
3---Bike-----Red
4---Bike----Green
5---Bike-----Blue

Sheet 2 (Database)
-------A--------B---------F
12---Auto---Red----11111
37---Auto--Green---22222
85---Bike----Red----33333
97---Bike---Green--44444
102--Bike---Blue----55555

View 2 Replies View Related

Pasting Cell Values Matching Destination Formatting?

Mar 31, 2014

I have browsed for two days looking for material on effecting this. The technique quoted most is watch for undolist for paste & auto fill then undo the change and paste the value again with specific formatting needs. This technique can be implemented in a worksheet_change event handler. The problem that I am facing is that the user can copy and past while my VBA is running. Once it is caught inside my own application class object event handler all that undo stack is already cleared. Therefore I have nothing that tells me if the user has entered the value via a paste action. By the time the value is pasted, all that formatting would have been entered. For example, a value that is interpreted by another workbook as DATE will have my destination formatting changed to DATE as well even though it is designed to be TEXT at design time.

So far I have not been able to think of a scheme to deal with this.

My basic intention is to always ensure the destination cell formatting remains as TEXT. If something is already interpreted as DATE after the paste even if I can convert to TEXT the string will appear totally differently. I have to find a way to paste the whole thing as TEXT in order to keep the string the same. The problem is that there is no intrinsic PASTE event in VBA. Without a method to undo that paste I cannot catch this at all.

View 1 Replies View Related

Matching Names In Different Formats

Sep 21, 2005

I have 2 reports that I want to merge which are exported from different applications. The problem is that the names appear in a different format and I can't work out how to match them up. The fact that not everyone appears on every report I have solved by using lookup as in the attached workbook. What I need to do is use a formula that will recognise the two different name formats and match them up. i.e. Campion, Ben P. and Ben Campion. See attachment.

View 6 Replies View Related

Find Cell Reference Based On Matching Data?

Dec 12, 2012

i want to search OK7V0T in all cells and return the cell reference beside matching data row

serial 1serial 2serial 3

box-4OK4KCUOK4KI0 OK4LLS
box-4OK60ZDOK62AP OK62QU
box-4OK6TA5OK6TBP OK6TFW

View 4 Replies View Related

Two Page Lookup Matching Criteria Based On Cell Value

May 26, 2009

I have an items list on page 2. On page 1 I want to create a drop down menu in C2 that references the list on page 2. Then when I choose an item, I want the additional information in cells D2 and E2 to come in automatically. The list on page 2 will be expanding daily, and when I expand it I can simply insert a new row so I correctly alphabatize it instead of constantly having to resort it.

With that said....
1. How do you put in the drop down box that references information on a different sheet?

2. What is the best way to reference the information in my ever expanding situation: Index/Match, Vlookup, Lookup, or something I havent mentioned?

View 14 Replies View Related

Changing Cell Colors For All Matching Textboxes Based On Text From Dropdown Menu

Feb 19, 2014

I have a large workbook, with multiple sheets. I would like to be able to select a name from a drop down menu, and have the cell color change for all entries of that name in my other sheets. I can do it manually, but it would be much cleaner and easier to have Excel do it for me. I have looked at other answers here on this forum and know that if it can be done,

View 1 Replies View Related

Conditional Formatting - Change Value Font Color In Cell Based On Matching Text?

Jun 26, 2013

Via conditional formatting, I am searching to change the color of a specific word in a cell (not the color of everything in the cell, but only that specific word - and the word repeats in the cell). A function such as =isnumber(search("NOK";A1)) colors all the cell values, which I do not want to do. How i can perform this task?

View 1 Replies View Related

Retrieving Values Based On Matching Values On Separate Worksheets

Feb 17, 2010

Is it possible to have a formula where it will retrieve a specific value on one sheet based on matching values? Unfortunately, hlookup wont work as there are several values in the table where the 'lookup_value' is the same.

Example:

On sheet 1 (titled BSRC), I have a table which has a list of values in column 1. On column two I have the 'lookup value' which needs to be matched and all values underneath it to be retrieved from sheet 2 (titled CSRD). The 'lookup_value' range is on row 9 of sheet 2.

I have attached a sample spreadsheet for clarification.

View 7 Replies View Related

Copying A Row Based On Matching Unique Values

Jan 26, 2009

I have the following code which takes a unique value from 'sheet 1', and searches for the value in 'sheet 2'. If there is a matching value in sheet 2 it will copy the full row into sheet three.

I need to extend the code so that all values from 'sheet 1' are copied to 'sheet 3' even if there is no matching value in 'sheet 2'. If there is a matching value in 'sheet 2' however I need the entire row to be copied to 'sheet 3'

Sub moving()
Dim c As Range, d As Range
Worksheets("Sheet2").Activate
For Each d In Worksheets("Sheet1").Range("A1:A500")
For Each c In Range("A1:A15")
If d = c Then
c.Resize(1, 70).Copy
Worksheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Exit For
End If
Next
Next
Application.CutCopyMode = False
Worksheets("Sheet3").Activate
Range("A1").Select
End Sub

View 2 Replies View Related

Minimum Returned Based On Matching Values

Mar 28, 2007

I am trying to do a spreadsheet for my boss and one piece of it has me stumped.

Based on same "die name" I need to return the minumum quantity available.

So, in column D i have a multitude of die names. Some of them match for those that match, I need to return in another cell the smallest quanity available in our die bank (column p).

View 10 Replies View Related

Count Unique Values Based On Matching Criteria

Jan 9, 2007

I have a worksheet with two tabs.

First Tab
Account Name
Account Number

Second Tab
Account Name
Account Number
Account Ship Location Number

On the first sheet each account name and number only appears once.

On the second sheet there may be multiple entries for each Account Name and Number.

On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.

View 9 Replies View Related

Calculate Average Based On Matching Values From Two Worksheets

Jan 22, 2010

Worksheet 1

I am calculating group averages for the following performers - very good, good, average, low, very low - for a series of factors.

Worksheet 2

Contains the same factors with the values for which Im trying to work out the average. Each factor has a performance rating above it, either very good, good, average, low, very low.

I need a formula which will match the performance rating from worksheet 1 (I3, J3, K3, L3, M3) to worksheet 2 and then calculate the averages of each factors based on those matches.

View 10 Replies View Related

Compiling Partially Matching Data Into Matching Rows With Macro

Jan 20, 2013

My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.

When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.

I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).

for example:

p1
data
data
data
data
p2
data
data
data
data

[Code] .....

needs to become:

p1
data
data
data
data

[Code] .....

I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).

View 5 Replies View Related

Matching Two Tables - Copying Data For Matching Value?

Feb 27, 2014

I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...

And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2

Table 1:
50025
50026
50086

Table 2: Considering that name, age and location is each in separate cell

50025JohnSecond25Location 1
60085EmilyThird 20Location 2
45454Wilhelm Fourth35Location 3
10000RoseFifth 60Location 4
50086JohnySixth 45Location 5
65501JacobSeventh18Location 6
50026Jackie Eighth22Location 7

And the outcame should be that it finds the value from Table1 in Table2 and copy's information in Table 1 like this:

50025 JohnSecond25Location 1
50026 Jackie Eighth22Location 7
50086 Johny Sixth 45Location 5

View 2 Replies View Related

Copy Matching And Non Matching Data Into New Worksheet

Feb 20, 2014

I have two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.

Matching data will be in sheet "Match" and vice versa.

Data are present dynamically.

View 1 Replies View Related

Cell Matching Range Copy Another Cell Same Row?

Sep 4, 2013

I have two excel sheets with order details.

Sheet1:A1:Order ID, B1:City, C1:State
Sheet2:A1:Order ID, B1:Rate

Sheet 1 only contains successful order ID's In Sheet 2 there are many order ids with pending, failed, successful transactions.

I want to compare Sheet1:A1 with Sheet2 full range Order ID's. If there is a match it should copy B1:rate to Sheet1 D1 and so on. I tried some VLOOKUP formulas but they did not work.

View 4 Replies View Related

Matching Cell With Or Without Spaces

Oct 11, 2013

I'm trying to match in the example, 'Name 1' which will pick the number below name 1. However if I have any space before, ' Name 1' in cell A1 than it won't match. I've put in wildcards in the formula but it still doesn't pick up the space? I need the formula to pick up either no spaces, or any number of spaces before or after 'Name 1'.

[Code] ......

I've attached an excel file showing the layout...

test_match.xlsx‎

View 3 Replies View Related

Copy Cell Value Down For Each Matching Value In A

Jun 16, 2009

formula below.

Crude and does not work.

I hope what I have said makes sense and can be written correctly.


'If WorksheetFunction.CountIf(Range("A:A"), Cells(i, 1).Value) > 1 Then
copy.Range("U:U"), Cells(i, 1).Value)=1
Cells(i, 21).Paste.Value.Count > 1

Basically, For Each Value in A that's identical. I need the value in "U" in the row for the first value in A to be copied down to the last row for the specific value in A.

View 12 Replies View Related

Matching And Cell Referencing

Jun 17, 2009

I'm trying to grab data from two sheets by first doing a match. The match works, but how can I now reference ws2 so that I can pick up data if this match is true?

View 2 Replies View Related

Matching One Cell To Multiple Columns?

Dec 27, 2012

How to match one cell against multiple columns.

I have numbers in columns a through cc and cannot put them in descending order. I am using this formula:

=match(CF1,A10:A1008,0) I have to use a new cell and type the same formula =match(CF1,B10:B1008,0) then again =match(CF1,C10:C1008,0) I AM RUNNING OUT OF ROOM! Is there anyway to search all columns =match(CF1,A10:CC1008,0) ?? This formula doesn't work when I type it in.

I have tried shift control enter for an array and still no go.

I have tried vlookup
I have tried Index
I have tried Iferror
I have tried isna

View 2 Replies View Related

Transfer A Row If A Cell Is Matching A Condition

Dec 11, 2006

I have 2 sheets in an excel folder and in fact I would like to create a macro to tranfer a row of the chart present in Sheet 1 if a cell is matching a condition so that the macro identifies each row of the chart in the Sheet 1 and transfer the row in the other sheet if the value in the column A (Chart Sheet 1) is equal to 1 :

sheets("Global Sheet").Select
Range("A1").Select
If activecell = 1 Then Transfer A1:J1 in the other excel sheet ( anywhere)

View 12 Replies View Related

Matching Cell References Using Lookup

May 15, 2009

I have a need to match a reference cell to a list of cells. How do I get it to match the exact contents of the reference cell, not just whichever is closest?
I'm attaching the sheet, it's important that the contents of A13 match a string in the AG column exactly or return a null or "none" or whatever.

View 3 Replies View Related

Extract 1st Matching Substring From Cell

Nov 26, 2008

I am trying to make a simple formula to populate a cell with the first matching substring found in a data cell (from a fixed list of substring keys).

Example of desired results:

Substring data: {X, YY, ZZ} (typically 6 to 15 values in real world use)

Data..........Calculated result..........Comment
AAX.................X........................Substring X was found in Data.

ABC.............[empty]...................None of substrings were (X, YY, ZZ were found)

XYYZZ.............X......................(only first match need be returned)

ABCZZ.............ZZ....................Substring ZZ found etc….

I am looking for something cleaner than nested if statements since there are likely to be a lot of values and different variations. The work would then be to copy the formula and populate the list of substrings to search for in the data. I tried some array formulas using search() but no luck so far.

View 4 Replies View Related

Matching Cell To Change Colors?

Apr 16, 2009

If cell A1 is the same thing at cell B15 then I would like cell A1 to turn RED. Is there a way to do this?

View 5 Replies View Related







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