Search Table & Return Heading Of Found Value
Sep 8, 2007
In a large spreadsheet that receives external data, I have codes (U, N or V) allocated at various times to different rows as shown in sample worksheet attached. Elsewhere in the spreadsheet, I need to display (for later export) these codes along with their respective number in a sorted list. Please review the attached:
In the spreadsheet you'll see a column of codes, the next column is the data reference number, then to the right is three columns, one for each code. As displayed in the sample book attached, each Code column is to display the data reference number (from column 2)that matches that column's code.
What formula can I use to list these numbers in the appropriate columns?
View 5 Replies
ADVERTISEMENT
Mar 1, 2008
Attached is a very small sample of a workbook that looks at community impact; before, during and after a project.
The source data (not shown) is down loaded from a system that can only supply the date in reverse order (see column A).
i.e. 20080229 = 29-Feb-2008. I've applied a conversion in column "C"....
= DATE(LEFT($A2,4),MID($A2,5,2),RIGHT($A2,2))
The 'properly' formatted date is then used to determine when on the scale of observation that given date falls.
e.g. the number of weeks before, during and after the project.
This is determined by the 18 week date 'matrix/ array' in columns F to X the red box is used to input the project start date, (& assumes the project runs to its normal 6 week time scale, it get REALLY messy if the boss prolongs or cuts short the project!)
From the result various figures and a graph using the properly formatted date are calculated, produced and presented showing....
The scale of the problem {before}
The impact of the project {during}
The lasting efftectivness {after}
You can see that I've managed a 'workaround' for the limit on the number of IF statements allowed in one formula using a rolling formula in columns; B then Z - AQ. This works OK, but!! combine that with the other formulae doing the impact calculations and 20000+ rows of data (with the acssociated workings in B + Z-AQ) the effect is to make make the file over 15Mb for 1!! worksheet, (the limit for emailing on the jobs system is 3Mb!!) & just to really make it scary sometime there are 2 projects on the go at once.
Before I created the attached the user responsible for these graphs and calculations manually counted dates & typed/copied & pasted the week number/tile, initially several days work followed by half a day every weekly update!
View 4 Replies
View Related
Mar 23, 2014
I uploaded a simplified version of the workbook I'm manipulating for reference. I'm looking for a formula where I can get the ROW heading of a table to be returned based on the max value of a certain column. My column headings in the table are months. I have separate place in the workbook where I have the Months listed in a Column (A9:A......) and in Cells (B9:B....) I want to return the row heading name from the table. The tricky part is that the column headings don't always stay in same place, so Jan isn't always in A1, sometimes it is in B1 or C1 of the table.
Essentially from my months listed is separate part of the workbook. In the cell next to each month I want to match that month with column in the table. In that column find the max value and return the row heading.
View 6 Replies
View Related
Feb 16, 2008
i have a workbook with several sheets in it. i would like to make a userform were i could put a number in a text box eg E045698.then use a command button to search all the worksheets for that number and display the sheet number where that number is in another textbox.
View 5 Replies
View Related
Sep 24, 2009
0000AAAAABBBBBCCCCCDDDDD
1111SugarRaisinDirt
2222MilkWaterWaterFlour
3333FlourSugarDirt
4444EggsWaterFlourSugar
5555WaterMilkDirt
I looked for a way to represent my data in a cleaner way than this but couldn't find any tools....a point in the right direction would be helpful. I guess the old plug-in that converted Excel->HTML isn't available?
Column A is a finished product and B-D are the ingredients to make it however values in Column A are also ingredients (sometimes multiple times). I want a formula that searches for values in Column A within the table B1:D5 and returns an array, if possible, of the finished products where Column A was used.
For example, searching for Sugar would return "Flour, Water". Searching for Water would return "Milk, Eggs" (I'd rather not have Milk listed twice but beggars can't be choosers).
View 9 Replies
View Related
Nov 10, 2009
I have been working on a variation of this for a long time and I cannot get it to work.
I am trying to depending on the textbox selection get the proper ROW from DEALLIST to show up.
View 2 Replies
View Related
Aug 22, 2013
i have a large table and i need separate x and y coordinates(to plot on a graph) of a particular repeated value that is known to me in a large table of numbers. how can i do this?
View 4 Replies
View Related
Oct 7, 2008
I'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A.
A1 production line_id
B1 poduction_order_number
A2 L1
A3 L2
A4 L1
B2 505212
B3 504234
B4 505663
I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D!
One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
View 2 Replies
View Related
Jan 20, 2014
I have a complex list of text (a chart of accounts) with various roll ups for subtotals on one worksheet, and monthly download on another worksheet showing the name of the account and the value. I wish to use match the text to return the value in the front sheet and avoid taking the simple route of linking the cells in case additions are added during the year.
I've tried using IF and VLOOKUP formulas without success - i guess becasue I cannot sort the table in descending order?
What would be the best sort of formula to search using the text, and return a value on the same row?
View 1 Replies
View Related
Apr 5, 2014
I have my data arranged in a table on sheet1 as shown below.
Columns B to H contain the hours each person normally works on the days of the week Sun to Sat. In columns I to O are the hours that each person has booked as holiday for that week.
On sheet2 I would like to be able to enter a holiday day in C1. In A3 to A15 I would then have a list of all the people who will be working on that day and in C3 to C15 have a list of the people who are on holiday so the output on sheet 2 would look like this.
Basically I'm after the formulas for A3 and C3 that can be copied down the column depending on how big my table gets. I am using Excel 2010.
View 9 Replies
View Related
Oct 5, 2012
I have sheet 1 that in cells V5:V20 is a data validation drop down box. In cells W5:W20 I have another data validation drop down box. On sheet 2 I have a table that in column A matches the data in the drop down box's in column V on sheet 1. Row 1 on sheet 2 data matches the data in the drop down boxes in sheet 1 cells W5:W20. What I'd like to do is on sheet 1 Cells Y5:Y20 have a VBA code to lookup the data in columns V and W from sheet 2 and return the value.
Windows 7
Excel 2010
View 9 Replies
View Related
Jan 12, 2010
I have a 10x10 table. First row contains column titles (plain text, hardcoded). Each line below them has a 1 in *just one* cell. So, each line contains 9 zeros and one 1.
In the 11th column I want to check which column has the 1 in it and return the column's title. So, if the 5th row has a 1 in cell E5 I should get, in K5, the value of E1 (the title of the E column that is).
View 9 Replies
View Related
Oct 7, 2013
I am looking for a macro to replace the values in a range of cells with a specific array for each column heading
Here is what I have
Temp1
Temp2
Temp3
0
0
0
1
1
50
[Code] ....
And here is the expected result of the macro
Temp1
Temp2
Temp3
no
false
min
[Code] ........
Please note there is only one Active Sheet.
View 2 Replies
View Related
Jul 13, 2014
Copy paste specific column with specific heading using macros. Roll No
Name
1
John
2
Abdul
3
Baig
1
I Want the Macro to find the column with heading "Name"
2
Copy the list from Name till the last Cell that is B1048576
3
Paste in Sheet2 Cell A1(Please see result in Sheet2)
View 1 Replies
View Related
Apr 14, 2010
I want to lookup the max value within a group of columns and return the column heading (specified a cell) that corresponds to the max value.
View 9 Replies
View Related
Sep 2, 2007
I have a spreasheet with partnumbers listed in column A, a safety stock value in colum B, a restock date in column c, and quantities listed by month needed in the following columns, i.e.:
A B C D E F G
Part # Safety Stock Restock Jan Feb Mar Apr
123abc 50 Feb 20 35 20 10
In the example above, it is apparent that the safety stock will be delpeted in Feb.
I am trying figure out how to calculate the month that the safety stock will be depleted
and show this in another cell.
View 6 Replies
View Related
Sep 16, 2007
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
View 4 Replies
View Related
Oct 11, 2007
How do you count the number of columns in a range in order to reach ( find) a specific value in that row ?
View 5 Replies
View Related
Mar 8, 2008
I have inventory in 9 cities. Cities names are in B2-J2. Line items are rows 3-100
In column K, I have the max formula for that row. What I need is, in column L, the name of the location city from row 2 where the max qty is located also color the max qty in the row.
View 2 Replies
View Related
Jun 9, 2008
I have range like (3E:3K) in this range are only 2 different expressions. "Result and " forecast". I wanna look from left to right for the first cell with the string "forecast".
And than I want to work with the column of this cell, only this column.
How do i do that?
Edited
I corrected the coordinates. I want to look in a specific column line (Nr.3) from there the first Cell with "Forecast" and from that String the row number.
They following 4 posts were based on wrong informations by me.
View 8 Replies
View Related
Feb 21, 2014
Encryption algorythim.xlsx
Here is my scenario I have all this data i want to search in. In the rows there are repeating values and in the columns they do not repeat.
As seen in the attached i am able to give a column and row by title and then get the intersecting cell data from that. (Encryption lookup)
What i want to do is give the column tittle and row value and get back the row title. (Decryption Lookup)
This shouldn't be too hard seeing that the data in the columns has no repeats.
View 6 Replies
View Related
Nov 18, 2013
I have a number of dates (columns) and under each date there is the demand value (Rows). Also, i have a column that has the current inventory. what im trying to do is to keep on adding the demand in one row (i.e multiple dates) until the sum just exceeds the inventory. After that, i would like to return sum the date at which we stopped adding. the point of this excercise is to see at which month will our inventory deplete according to the demand. Below is an example solved by hand.
Demand
Part #
month 1
month 2
[Code].....
View 9 Replies
View Related
Nov 18, 2008
I have a list of names and the chores they need to do on a certain day. I need a formula that returns the chore the person needs to do when the date is filled in.
Ex: Sheet2 Col:A has names filled in A2:11 , row B1:J1 has dates that you would input.
formula goes in B2:J11
Sheet1 is the master with all the data the formula would be pulling from.
column A2:A11 has the names , row B1:S1 has the chore that needs to be done.
B2:S11 has the dates already filled in.
View 5 Replies
View Related
May 26, 2014
I have a pivot table that looks like this:Red Yellow OrangeApple .................12
Banana ......................10
Strawberry ..........8
Carrot ..................................2
I want to know the colour of each fruit, for example:
Apple Red
Banana Yellow
Strawberry Red
Carrot Orange
What formula should I have in the second column to find the colour of the fruits?
View 2 Replies
View Related
Jul 11, 2014
I have a data set, in which Sheet 1 have a matrix of high level showing Engineer, Program and commodity relationship.
I have to create separate sheet for each program on sheet 1, each program sheet should have Engineer name and commodity name from sheet 1.
Multiple commodities and multiple engineers should be separated by definite structure.
find worksheet with input and output (manual) as expected.
View 2 Replies
View Related
Apr 11, 2014
I was going through "To do list with progress tracker" template in Excel 2013. I noticed when i scroll down the page the heading column (A,B,C,D,E,F,G etc.) automatically matches with the inserted table headings.
View 1 Replies
View Related
Feb 8, 2012
I manage to do a proper search and the return value is correct, but the problem is when I don't get the correct value excel gives me an error, what I can add to make the result just to give me MsgBox "Not found"?
Code below:
Set Ran = Worksheets(2).Range("A:A").Find(CompName, lookat:=xlPart)
If Not Ran Is Nothing Then
MatchRow = Ran.Row
MatchCol = Ran.Column
End If
View 5 Replies
View Related
Feb 29, 2008
I am looping in Excel to find "NUMBER OF EMPLOYEES". IF found I want to go up one row and replace the first 4 digits with " 9ZZ". The Line 1 data will alway be different so I just want to replace the first 4 digits. I don't want it to effect the rest of the line.
Example:
Line 1 - 100 6300 BRCH TOTALS
Line 2 - NUMBER OF EMPLOYEES
RESULTS NEEDED:
Line 1 - 9ZZ 6300 BRCH TOTALS
Line 2 - NUMBE OF EMPLOYEES
View 9 Replies
View Related
Aug 30, 2007
I have a workbook with a sheet for every month. I have a searchbox searching for a client and jumping to that location. The problem I am having is that it takes me to the last occurence in the workbook if there is more that one client with the search criteria. Do you have any suggestions for a searchbox that whould take me to the first occurence and give me an option to go to the next occurence in the workbook. Please see the code below that I am using currently using.
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim cl As Range, rng As Range
For Each ws In ActiveWorkbook.Worksheets
Set rng = ws.UsedRange
With rng
Set cl = .Find(Me.TextBox1.Value, LookIn:=xlValues)
If Not cl Is Nothing Then
Application.Goto cl
Else
View 7 Replies
View Related
Sep 5, 2007
I need a VBA code that will search column H for any value that DOES NOT start with "9" (this is a character field). If it finds a cell that doesn't start with "9", I would like it to delete the entire row. It will need to repeat this process for every cell in Column H that has a value and then stop.
View 7 Replies
View Related
