Results From Two Matching Criteria
Mar 20, 2007
I am trying to solve a small problem that i have, I am not that good with VBA and need somehelp.
heres the situation.
Sheet 1 is filled in col.1= title, col.2= table# (ex. 1,2,...11)col3.= grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10). I will enter a title then pick a table then a grid location.
On sheet 2 also known as table 1. col.1 =grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10) col. 2 = title.
I want to create a macro or function that can evaluate sheet1 and pick out when col.2=table 1 and col.3 = grid loc. A-1.... through D-10 then give me what is in col.1.
this will have to be done for 11 sheets 1 sheet for each table.
View 9 Replies
ADVERTISEMENT
Jul 14, 2009
I have a spreadsheet schedule that pulls quantities from another spreadsheet's cells. if the part number of the reference row matches the schedule cell's part number (row), and the date of the reference cell row is 2 workdays after that of the schedule cell's column, the quantity is put in the right place in the schedule. that part works. the problem I have is that sometimes there is more than one quantity with the same part number and date (due to the parts belonging to different jobs). when this is the case, only one of the quantities is picked up by the schedule. i want the schedule cells to sum the quantities of the reference file with matching criteria. i also don't want to have to change the reference file at all in order to do this. below is the function that works (besides summing the duplicates). the part after the " " is what i would need to alter.
HXS is the reference file where D is the part number column, I is the date column, and 5 is the index column referencing the quantity.
C is the part number column in the schedule
K is the date row in the schedule ....
View 10 Replies
View Related
Jun 20, 2006
Here you can see 4 columns (C, D, E, F)
I would like to compare Column C, D, E, F and If column C = Column E., i would like to show the result from column F to Column d.,
For example:
For column E >> The result in column F is: Port Blair.,
I would like that to be in Column D after a match.
Is there any forumula which will do this.,
View 5 Replies
View Related
Nov 10, 2009
I have a wordlist (65000 words in Column B*) in a worksheet w1 and poems (about 21000 rows) in another worksheet w2 where first verse is always in B and second one in C. The column D of w2 contains of information such poems name, authors name, books name and so on.
I want to search for those verses through a macro which contain words from my wordlist B* w1 and add references to my words that way. Its about making a dictionary in which each word has a reference to a poem and verses in which it is used.
Column A of w2 has number in it which shows how many times these rows have been already used as reference. Column A of w1 contains of a value 1 or 0 in which 1 means this entry has already been processed or already has a reference and 0 means it has yet to be done.
If my word B* from w1 exists in verses B or C of w2 the results should be shown in an userform containing 5 text boxes with 5 results. In each text box a result should be shown (verse B; verse C); (value of Col.A) and a checkbox (or a button). That would mean 5 result, 5 textboxes and 5 checkboxes (or 5 buttons). I would like then to choose one of the results by checking the checkbox (or pressing the button) in front of the text box. After I have chosen the result the whole row from w2 should be pasted in following columns of B*. That means the cells from w2 column B,C,D would be pasted to column C,D,E of w1.
The criteria for the search in B and C should be the lowest value in A of w2. I mean the w2 rows with lowest A value should be preferred if there are more results. The result showing user form with 5 text boxes and 5 checkboxes(or 5 buttons) which gives me the choice to choose one of the results should also contain a button search for further results. For the case none of the results is useful.
As far there are many poems (verses) I would like to prevent using the same verses many times. The more different verses I use as reference for the words in w1 the better it is. Therefore every time a result is picked by me 1 should be added to the value in Column A in w2. And every time search is started the lowest A values should be searched first.
As soon a word has successfully got its reference the value in A w1 should be changed from 0 to 1. And by next search all words with A value 1 should be ignored and only 0 words should be searched for.
Each time a row from w2 is used as reference, the B* word w1 should be added to col. E of w2. If used for many ";" should be the seperator.
View 14 Replies
View Related
Dec 7, 2008
I am trying to set up a lottery spreadsheet, where I enter the results and matching numbers would be highlighted
View 9 Replies
View Related
Feb 13, 2014
I have 2 worksheets, A and B. In both worksheets there is common data in column A (account ID). I would like to find a way to return all of the data for the matching row in worksheet B and have it pasted into the matching row in worksheet A.
So in the example below, I am looking for a function that will match on Account ID in both worksheets and then paste the results from Dataset 1, 2 and 3 into the row with the matching Account ID in worksheet A.
Worksheet A
Account ID
Column to paste matching rows from Worksheet B
1
2
Worksheet B
Account ID
Dataset 1
Dataset 2
Dataset 3
1
AAA
BB
CC
4
EE
DD
FF
View 1 Replies
View Related
Jul 9, 2012
want to match column A to column C and if they match take whats in column D and put it in column B.
Column C has multiple instances of Column A and I would like each instance's, which is the following column, value put into column b
View 2 Replies
View Related
Dec 23, 2008
I have an output table that looks like the followings:......
There is 1 input for the spreadsheet: What month? Enter 9, 10, 11 etc. I need it to pull from a data sheet that is set up as such:......
So effectively it needs to vlookup the name in column A, (A, B, or C), and then it needs to match that to the column that has the same name as the output table (Crit.1 or Crit.2 and the number of the month above it (10,11,etc). I attached an excel sheet to better demonstrate
View 2 Replies
View Related
Dec 23, 2009
If you look at my attached example. I'd like the date line/columns in Sheet "8002372008" to be populated automatically with data from Sheet "Data".
Is this possible? VLOOKUPs and MATCH are what I was thinking, but I'm not sure how to do it.
I know it could probably be done in VB, but I don't want to have to click on each "800 Number" sheet to update it every time, so that my summary sheets are accurate.
View 10 Replies
View Related
Jun 30, 2007
I need to match data with specific criteria. Details of the problem are as follows:
I have 2 sheets: "Main" and "Sub"
In "Main" there are 3 Important Columns: Col "C", "I" and "AF"
In "Sub" there are 2 Important Columns: Col "I' and "N".
Value in column "C" of "Main" is same as value in column "I" of "Sub", but not in same order.
Now the specific criteria:
If value in Column "I" of "Main" is not equal to "Zero", then the macro should pick up the value from the corresponding column "C" of "Main" and search for the same in column "I" of "Sub". After matching the same, it should find corresponding value in column "N" of "Sub". Then the macro should pick up the value from column "N" of "Sub" and put the value in the column "AF" of "Main".
This is basic requirement.
I am unable to retrieve the right value for the same.
Fine tuining will be:
If the value in column "N" of "Sub" is "Fully Automated" then it should only write "Automated" in column "AF" of "Main". And If the value is "Manual + ...." then it should write "Manual" in column "AF" of "Main".
View 14 Replies
View Related
Feb 9, 2006
Col cCol dFormula
Lease TermCompany #6023%
3637%
4843%
6052%
12620%
247FALSE
368FALSE
48105%
609FALSE
Residual Table
Company #
127
438
569
10
Col b
Lease TermResidual %
1210%20%9%
245%9%6%
364%7%3%
483%5%2%
602%3%1% ......
View 10 Replies
View Related
Apr 22, 2012
Modifying the following line by replacing the row number in AC2 and Q2 by an incremental variable? I mean something like βACβ&Rw , βQβ&Rw, &Rw, Rw being my incremental variable used to go through the rows.
ElxJobNo=Evaluate("Index(Index(Jobnumbers,0,1),Match(AC2&Q2,Index(Jobnumbers,0,3)&Index(Jobnumbers,0,4),0))")
View 3 Replies
View Related
Jul 6, 2006
How do I return an offset value within a named range using a formula? For example, my range "RngTest" is from C3:J43. If a value of "Product123" is found within the range, (lets say its found in cells C3; E5 and E7), I need the accumulative values in the cells 'below' (in cells D3; F5 and F7) added ( SUM) and returned to cell L3.
Therefore if Cells D3; F5; and F7 have the respective values of 3; 4 and 5,
the value of cell L3 should be 12. Also, do I need to express "RngTest" or "C3:J43" or does it matter?
View 4 Replies
View Related
Feb 14, 2007
I have a spreadsheet that holds salary information. At the moment it shows against each person the highest salary for a person of the opposite ***. What I would like to do is have another cell that shows the names of all people who have that salary.
E.g. if the highest comparable salary for someone of the opposite *** is 30000 then I would want a list of all the people with that salary.
Is it possible to do a look up that would show all the results that match certain criteria?
View 11 Replies
View Related
Apr 5, 2013
I'm trying to build a formula on the matching text as below, im getting an value error, however when i press the FX key it shows the correct result.
Column A
Column B
Column C
Column D
[Code] ....
( I would like to match the below two criteria with the Row 1 and Row 2)
Co1
#VALUE!
Latest Forecast
Formula-->
IF(MATCH(A6&A7,B1:I1&B2:I2,0),"Ok","Not ok")
View 1 Replies
View Related
Jan 30, 2014
I'm trying to create an overview which shows a breakdown of the total minutes for each task on each given day.
I need to get a formula to work in the grey area which sums the total minutes (column C) based on matching two criteras of task (column A) and day (column b).
View 5 Replies
View Related
Jan 30, 2009
I have two sheets where I dump data into. Widget sales and Widget Accessory sales. I'm trying to display for the widget salesmen the amount of dollars they sell in widget accessories ONLY for the transactions in which they also sell a widget at the same time.
So I need a formula or process (taking and comparing the data from both sheets) to do the following
IF
1. The dates of a widget sale and the accessory sale match (in both sheets)
2. The salesman ID in both sheets match
3. The names of the customer in both sheets match
THEN
1. Add up the total dollars by salesman for sales that match the above criteria
I've tried using sumproduct... I've tried using countif and concatenating pieces of data to make unique sales codes... I've exhausted my meager excel skills .
View 4 Replies
View Related
Jan 8, 2014
I have a sheet containing a list of client name,the date they've been assigned, the type of account they have, the status of the account, and the last update date.
What I'm trying to accomplish is to extract the client name and the type of account on that table that shows as "complete" in status. However, it gets complicated since I need to separate the data that had been 'completed' under the same month, with those completed the other month.
For example, I have been assigned 2 clients this January. I closed one in the same month, and the other closed at February. The first client would be listed under the first table (Completed within the month) and the other would be on the second (Completed on previous months)
Since this would be monthly, I am thinking if it would be more efficient to include the table per sheet (as one sheet corresponds to a month), or consolidate everything to a single sheet... I'm not sure which one would be best.
View 2 Replies
View Related
Aug 31, 2013
I need to compile a 'sumifs' formula to add sales for the salesmen.
Below is an example table. My dilemma is determining that in addition to the sales I only want to 'sum' the 'CANCEL' sale of a customer that initially purchased a 'GOOD' Status and not a 'PENDING' status.
How can i write a formula to determine that (for example) Customer3 'cancelled' the 'pending' sale, therefore I do not want the 'cancel' to sum in my calculations.
Date Salesman Customer Product Status Sale
Jan 8th John Customer1 Apples Good $500
Jan 9th Mark Customer2 Pears Good $200
Jan 8th Kevin Customer3 Oranges Pending $250
Jan 15th Mark Customer2 Pears Cancel $200
Jan 16th Kevin Customer3 Oranges Cancel $250
View 4 Replies
View Related
Jun 28, 2014
I have a spreadsheet where I want to match the PO info with the invoice info. I want to match the cost center, location, PO number, item number and the price.
Period
Cost Center
Account
Item No
PO No
Amount
Test
DEC-12
5000AF
000
M0002648
13579
(59.89)
5.11
[code]....
The formula I used in the test column
=SUMPRODUCT(--($B$2:$B$13&$C$2:$C$13&$D$2:$D$13&$E$2:$E$13=B2&C2&D2&E2),$F$2:$F$13)
As you can see in that the formula is showing the first 4 rows with a difference even though that the difference is in the first two. How can i fix this?
My second question, on the lines with the 989.04 difference, how can I make the same formula to say "Fix the account" or to fix the column with discrepancy
View 4 Replies
View Related
Jan 3, 2007
setup a vba code to find values which were separated by a certain value, thus there would be pairs of numbers sought after. What occured was that the first value is bolded, followed by the second not bolded, this pair would be separated by 75. Now I want to apply the same code, but instead of copying just the two cells which were separated to someone on the current sheet, I would like it to copy the entire row (as there is more data associated with it now) with the same bold/not bolded pairing to a new sheet, called "Numbers". I would imgine it be straight forward, yet I am very new to this and still tinkering.
Heres the ....
View 9 Replies
View Related
May 11, 2007
I have been using the code found here
Sub DeleteRowsFastest()
Dim rTable As Range
Dim lCol As Long
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else.............................
to delete rows that match the given criteria. I am now wanting to do the opposite, keep the rows matching my given criteria and delete all others.
View 3 Replies
View Related
Jun 25, 2007
I have been asked to analyse the contribution / performance of each student in a team. What i am trying to do is to count the number of times each student has work together and then try to work out their contribution. Got stuck on the 2nd bit for like 3 weeks flat. have been trying but not luck... seems to give me 1 or 0. so i assume my attempt is only a true or false answer! All i need is a count of how many times each student work with each other, and the best backbone formula is "count". This situation is best explained by my example, D15 is the closest i got
View 3 Replies
View Related
Sep 21, 2012
Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)
I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.
Obviously to size of the list will vary depending on how many open orders there are.
View 1 Replies
View Related
Apr 30, 2014
I'm trying to evaluate if 2 of the 3 criteria are met using an IF function in Excel 2010.
I have 3 columns of data. Each column has a number ranging from 0-15.
Example:
B2 = 0
C2 = 3
D2 = 4
Since B2 = 0, I'd like to have the equation return a 'Yes' because there are values greater than 0 for 2 of the three cells.
The hangup is that any of the three cells could potentially equal 0, so the equation must account for C2 or D2 being zero.
View 4 Replies
View Related
Jun 19, 2009
I have a conditional format that does not seem to be working for me. Cell B2 has a drop down optionSelect, No, Yes); Cell B3 is supposed to be conditionally formatted to return the following results if the criteria is met:
If B2 is equal to No or Yes then colour should become Yellow
If B3 is >0 then colour should become Blue
The problem is when B3 is greater than 0 it does not change the cell colour to Blue.
B3 Conditional Format #1 is =AND(ISTEXT(B2),B2<>"Select")
turn background to yellow
B3 Conditional Format #2 is =AND(ISNUMBER(B3),B3<>0)
turn background to blue
See attached for spreadsheet with conditional formats
View 3 Replies
View Related
Jun 20, 2007
If I have the following table:
Name ProductSize QuantityPrice per unit
AX35510
AX451015
AX551210
BX601010
AX35510
I now want a resulting table that will combine all rows where name, produc, size and price are identical and have one row with the same data apart from the quantity that will have been added - so for the table above the resulting table will be:
Name ProductSize QuantityPrice per unit
AX351010
AX451015
AX551210
BX601010
View 9 Replies
View Related
Dec 1, 2006
Please see the attached sheets.
Here's the issue:
On sheet "master" I have a list of goals in column A.
Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.
I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.
I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.
Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.
how this done for AL, I can go ahead and create the other sheets.
View 9 Replies
View Related
Jun 9, 2013
I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. Please note that there is no fixed number of inputs for any one group i-e the solution has to be dynamic.The groups are numbered from 1 to 6 and number of groups is fixed i-e 6.
A sample file illustrating the inputs and required output is attached.
I also posted this on: [URL] ....
Sample_file_summary.xlsx
View 2 Replies
View Related
May 2, 2014
how to figure it out this lookup problem (lookup using partial string of match)...
View 5 Replies
View Related
