How To Use One Cell To Match Pairs Of Cells For Missing Items
Sep 13, 2009
We use a program at work to check items out to individual people but sometimes an item is missed and it takes forever to manually look for who it is. In the attached example, (keep in mind there might be more than 100 people on this list...this is just two people), the first 2 items are a pair (radio and radio case) and the second 2 items are a pair (laptop and laptop case).
Id like a way (condition formatting) to flag a person who has one item from the pair checked out and not the other. (Ive manually highlighted one person who only has one of the paired items...id like this to happen automatically)
EDIT:: If the first person were missing the radio in this example, his name would be highlighted on the line with the matching pair...cell D1 (radio case)
Other information:
Radios will always be RFAMRC***
Radio cases will always be RFAMF****
Laptops will always be RFAMTD***
Laptop cases will always be RFAMTE***
I have a spreadsheet with 14, or maybe 16, or maybe 40 names in column B.
A1 = 1 B1 = JOE CITIZEN A2 = 2 B2 = JANE PERSON ... A17 = 3 B17 = JACK DOE
and so on...
I want to randomly assign these names to pairs using a macro button, and basing it on the number of players (C1)
So if C1 is 12, I'd like the function behind the macro button to come up with 6 * 2 numbers (2 and 11, or 4 and 7,
all completely random, but within the 12 specified in C1) and write these random values in Range D1:E6 (if C1 is 18, then the range to write in would expand to D1:E9).
I don't know enough about writing code to be able to pull this off .
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
I'm trying to use DGET to pull coordinate pairs from a list and match them up to a label based on certain criteria. I can't seem to get it to work. I'm also thinking that the way I have it set up, the coordinate pairs might not stay together and that has to be the case. My criteria is multiple cell based too and I don't think DGET can handle that.
For example, In order for a coordinate pair to be labeled "B" the x coordinate must satisfy a condition and the y coordinate must satisfy a different condition. Can I use DGET with the AND function?
I have a workbook that on sheet one I mark qualifications with an X. On sheet 2 I use a formula that places an X in cells if all requirements are met example:
Is there a formula I can use that if one of the requirements are not met it will tell me what is missing instead of me going back and have to look through all the data to find out D5 was missing as an example?
I have created a table that has working hours of staff members over many weeks. Week number as column headings (1 to 52) and staff name as Row headings. E.g a row may be
John Smith, 37, 37, 37, 37, 64 (commas to show seperate cells)
How would I go about using conditional formatting so that the formatting changes according to the sum of the values in each pair of cells?
I need to add the total hours of every two weeks for some staff and change the fill colour of both cells accordingly to highlight which weeks staff have worked too many/few hours.
So (B1+C1) would be a pair, the total would decide which fill colour is used on both B1 and C1, and then (D1+E1) would be the next pair and so on.
I have tried using 'a formula to determine which cells to format' and placing =(B1 + C1) = 74 and making it fill the cells green but this appears to be doing (B1+C1) as the first pair and then (C1+D1) as the second and changing the format for the first cell only.
So I have a spreadsheet that has a Title in Cell A1, then entries in B1, D1, F1, H1, J1, etc... with empty cells between.
What I would like to do is copy those entries to the right, i.e. B1 into C1, D1 into E1, F1 into H1, but all the way along because in my master sheet there are a lot of columns.
I've got 3 pairs of columns and I need to sort through them and align the cells in columns E&F with those in A&B and C&D. The cells I need to match up are the times (columns A, C and E)
I'd like to generate a total sum based on the quantity from each item. So columns H3 - N3 should generate a total in P3 (skipped O) by the quantity entered in each cell. In addition I'd like the value of D3 to change the pricing in the formula. D3 has a drop down with 3 products, so selecting ANY of those products should cancel out the previous formula and replace it with another.
This is the sum without a value in D3 =SUM(H3*10.00)+(I3*8.00)+(J3*6.00)+(K3*18.00)+(L3*10.00)+(M3*8.00)+(N3*8.00) IF D3 has a package selected from the drop down, I'd like this sum to generate instead. =SUM(H3*8.00)+(I3*6.00)+(J3*4.00)+(K3*15.00)+(L3*8.00)+(M3*6.00)+(N3*6.00)
I want to match let's say "Elbow" with "Yes" so that when I select the word "Elbow" from a list the word "No" appears on another cell. There are 54 words to match. This is what I'm currently using, however, I could only get to 29 items and I need to match 54.
I have a database where I have groups defined. For example, Parent 0001 is called Bag1. It contains an apple, orange, grape, and a pear. Parent 0002 is called Bag2, containing an apple, orange, and a peach. So on and so on...
Below is an illustration of what I explained above:
I need a way to input items that are in the groups and find the groups have all of the items or the group with the best match. I need to know if the match is 100% or if it is only a partial match.
The bag might have 2 items or it could have 20 items and they can be in any order.
I need to be able to count items if they match a condition in another column, as below
A B 1 Apple Red 2 Apple Green 3 Apple Red 4 Pear Green 5 Apple Red 6 Pear Green 7 Pear Red 8 Apple Red
How can I count how many Red Apples there are. The answer should be 4 but when I use countifs I can either count the no of apples in Col. A or the no of colour Red in Col. B.
I have tried every combination of if, vlookup, and count that I can but keep getting errors or single col. counting.
Though i wanted to rid the Na error from it when C is blank
I seen a few ways, And wanted to try the method of using Countif becouse it can be used withen the same cell without using extra cells like another method i seen "=IF(ISNA(A1),0,A1)"
what i know of countif This function counts the number of items which match criteria set by the user. =IF(COUNTIF($A1,"")),LOOKUP($C1,$A1:$A1,$B1:$B1),"")
so how the heck can the countif function be used to rid the NA of a lookup. i just cant see a relationship between counting a lookup.
I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish:
On sheet1 there are three columns, Business, Amount, and Closing Date.
Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option.
On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.
I have two columns which i want to compare, they contain text data such as A123.
what I'd like is if its in column A and not in Column B then add to bottom of column A.
Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.
I am trying to work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.
I have a condundrum that having trouble solving hoping someone may be able to help...
I am trying to return the contents of all cells that match a value based on another cell.. I have managed to achieve with the forumla below but it only returns the first value matched.
what I wish to do is return all values in ROW E where the value in ROW B equals Deliver and combine into one cell [F1].
ie. B E F DELIVER VALUEW VALUEW VALUEY COLLECT VALUEX DELIVER VALUEY
The formula I have currently uses index but only returns the first value found. I need to find all values and place in one cell. C5 holds value 'deliver'
I have a spreadsheet which has number of formatted rows, the user can insert the formatted rows if they need more as its hard to determine if they will need one set or 50 sets.
The formatted cells are 4 rows deep and span from columns A to AR
The thing is that I want the totals at the bottom to automatically pick up the sums from say Column H but only on the 2nd row of each set of formatted cells eg. H13, H17, H21, H25 etc.
And another sum to pick up the 3rd row, e.g H14, H18, H22, H26 etc
Obviously this can be done manually by selecting control and AutoSum but I wondered if there was a pice of VBA code or a formula that i could run due to some worksheets being longer than others?
I am trying to do something with some data and I am not sure if it is possible. Even if it is, I have no idea how to do it. I was wondering if you could help.
I am trying to get an average of cells that are in the same row as the last 4 cells to match a certain criteria (which happens to be a cell).
Please see attached spreadsheet for example as it is very hard to explain.
D2 = the average of the next 4 figures in column E that are in the same row as the next 4 team names from column B to match B2
For example D2 would be equal to: AVERAGE(E29+E48+E72+E86)
For example D86 would be equal to: AVERAGE(E105+E122+E152+E177)
If this doesnt make sense, please ask and I will endeavour to make it clearer.
Example spreadsheet: Spreadsheet attatched in post 4.
I have a row D2:ND2 where each cell contains a date from October 1st through to September 20th). I also have a column NI3:NI13 that contains various dates such as Easter or bank holidays. Would it be possible to use conditional formatting (or something else) to highlight all rows below D2:ND2 if the dates specified in each cell in D2:ND2 match any of the dates in column NI3:NI13?
I have 2 TextBoxes (TB1 & TB2) in which a store name and a date are entered. The sheet has many store names and many more dates(shop at the same store on different days). I am trying to get the total of columns L, M & N if TB1 = Column A AND TB2 = Column B. I know where to place the code (in the exit event of TB12(L), TB13(M) & TB14(N)). I have added sample data and updated my sample to make it easier to see what I am trying to do. Can anyone show me how to match up these TextBoxes and cell values or give me a hint as to how to proceed?
The following macro searches for missing combinations. This macro will search the complete list and will return any missing combination from "1, 2, 3, 4" to "7, 8, 9, 10".
I need to make some changes in this macro, so that it will search for missing combinations only within a specified range of cells (and not the whole list). For example (see excel file attached), I would like to place a search within range("G23:J183"), from combination "1, 2, 6, 9" to combination "4, 6, 8, 10". In this case, it should return only 9 missing combinations.
Attached File: Example Find Missing Combinations.xlsm
I've written a very simple script to replace carriage return characters with <br> tags (so I can use the output in html pages) - however for some reason when I loop the script down the cells, it ignores certain cells and works perfectly on the rest.
The only 'variable' I can spot is that the ones it misses tend to be longer cells with more text (the one's that failed were 938 characters and 910 I think).
Can anyone tell me if there is a limit on how big a cell VBA can process and if so, how I can work around this? Is it possible to load and parse each character one by one in VBA or something?
Private Sub CommandButton1_Click() For Each cl In Worksheets("CREDIT (GENERAL)").Cells.SpecialCells(xlCellTypeConstants, 23) cl.Replace What:=Chr(10), Replacement:="<br>", SearchOrder:=xlByColumns Next cl End Sub
