Return Values From Several Columns
Feb 19, 2009
If I am creating a vlookup forumulae, and I want to return values from several columns, I have been manually changing the column it's returning the value from when I drag the formula across the columns. This is ok for a few columns.
eg =VLOOKUP($D2,$AB:$AJ,1,FALSE), dragged across columns, and then manually edited in each column to read
=VLOOKUP($D2,$AB:$AJ,1,FALSE), =VLOOKUP($D2,$AB:$AJ,2,FALSE), =VLOOKUP($D2,$AB:$AJ,3,FALSE), =VLOOKUP($D2,$AB:$AJ,4,FALSE)
etc
The value doesn't increase when you drag it.
is there a short cut for doing this automatically?
View 10 Replies
ADVERTISEMENT
Apr 17, 2009
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.
What would be the best way to do so?
Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.
I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
View 7 Replies
View Related
Nov 26, 2008
I have 8 columns of data, the first 4 and the last four columns have the values which correspond to each other.. Here is an example:
VALUES(A to D)NAMES(A to D)
ABCDABCD
1.3451641.3383581.286221.359808321325370391
So value 1.345164 corresponds to 321 and 1.28622 to 370. I need a function that will return a value from one of the VALUES columns which corresponds to the second smallest cell value from the NAMES columns. For example – here the second smallest number in NAMES column is 325 so the function would return the corresponding value of 1.338358.
View 9 Replies
View Related
Mar 20, 2014
I have a two different workbooks book 1 and book 2 with some data.
If values of first two columns ( Column A and B) of both workbooks are matching then I want a formula that can return the value of third column (column c) of book 2 in book 1 column c.
example:
Book 1
column A / Column B / Column C
100 / 200 /
Book 2
Column A / Column B / Column C
100 / 200 / 300
now I want a formula that can return in column C of book 1 to display 300.
And the data runs across some 100 rows in both workbooks.
View 4 Replies
View Related
Feb 26, 2013
Vlookup or Match for a product list project.
I need "my sheet" to populate (columns A,B,&C) with some source data; however, I need the lookup to reference both retailer# and color (columns D&E) and return other information such as description, color description, etc...
Here is the example of both sheets:
My Sheet:
Row/Col.
A
B
C
[Code]....
View 1 Replies
View Related
Oct 24, 2006
I need to know how to read all the criteria1 brought by the autofilter (in the dropdown box) from one column one by one and then print the table. Is that possible? I've read that it's hard but never read how to do that.
View 4 Replies
View Related
Jan 30, 2008
I have 2 spreadsheets, I need to collect the data onto 'figures 07' from column D on 'spreadsheet 07' by date, based on the name matching. ie cell B4 on 'figures 07' should be 166 and cell C4 should be 72. I've tried IF and LOOKUP but they don't give me the correct answer Auto Merged Post;Here is the second spreadsheet, it wouldn't let me attach both of them on my first message
View 4 Replies
View Related
Feb 8, 2013
I got a good start on what I need to do from this thread here: [URL] ......
A user will use a userform to enter in results from a room inspection into one sheet and then on another sheet selects the maid and it pull up the matching room inspections. I wish to then limit it to a date range which can be found in two cells.
Currently cells D2:H2 contain the array
[Code] ......
and cells D3:H3 contain
[Code] .........
I would like to further limit those searches by restricting the date range, Cells D4 and E4 contain the first of the month and last of the month respectively.
I would like to avoid the easy answer, start a new workbook each month, but I won't be the person entering the data or using the separate sheet to conduct performance reviews so it needs to be one workbook that lasts from month to month.
View 1 Replies
View Related
Mar 17, 2009
I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value.
I have a column between the years that calculates the percent of gross revenue for the specific department/cost.
I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever.
The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.
View 7 Replies
View Related
Dec 11, 2012
I'm trying to find a way to:
Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"
Things to note:
a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.
b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".
If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".
If that value does not occur in sheet B, the corresponding cells should remain blank.
If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.
View 2 Replies
View Related
Feb 21, 2014
I am trying to create a macro that will compare several un-formatted workbooks and copy just what I want to a workbook of my one.
I start by having 2 different workbooks opened ("Main" and "Change") side by side like this: 1.jpg
Then i run the macro in my "Main" workbook and it should appear an Userform like it showed in the image below: 2.jpg
This is so that i can manually fill the user form with the correct columns by looking at the excel file, for example:
Ref=E3;City=G3; Data=I3.
Pressing Ok, it fill the "Main" workbook with the matching information so it and closes the "Change" workbook.
At the moment my code is in this stage:
VB:
Private Sub CommandButton1_Click()
myfile = "C:Userssst1brgDesktopRelatorios" & TextBox1.Value
Application.Workbooks.Open Filename:=myfile
Windows.Arrange ArrangeStyle:=xlVertical
[Code] .....
But I am stuck because there isn't any error and nothing happens.
View 1 Replies
View Related
Jun 20, 2008
I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.
EG
Store 1 1111 16:40
2222 13:00
Store 2 1111 15:05
3333 16:50
View 9 Replies
View Related
Oct 23, 2008
I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.
View 3 Replies
View Related
Oct 13, 2008
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
View 4 Replies
View Related
Mar 26, 2014
I have set of data in multiple range ,need to fill the and replace the old values depends upon two column values (AH & AL)
IF Active Calls is "TATA" In AH:AH, and IF Action Onwer Col is "Blank",in AL:AL
Then Fill the Blank cells by Values "SVC" in the col Action Owner,Then Replace Old values by "Updates Awaited" in Status Col(AM:AM)
Find the attachment & basic code take this code for this task
[Code] ....
toggle-2.xlsb‎
View 5 Replies
View Related
Mar 12, 2014
I have some value sorted numerically (according to ID column) but there are some missing values. In a new column (Desired value column), I would like to return the values (according to ID column) and add missing values (according to Order column) as zero.
View 2 Replies
View Related
Mar 14, 2014
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
View 2 Replies
View Related
May 15, 2014
I would like to align the matching values in columns A and B but I want the values that correspond to b in c,d and e to go with the column b value.
C D and E don't necessarily have a value in there but if it does it needs to move with B
View 5 Replies
View Related
Jan 15, 2014
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
View 7 Replies
View Related
Oct 7, 2011
I have to lookup values from 2 columns and return a value from the 3rd col. I went through previous posts on this forum and was able to get the values. But I corrected the data and am trying to do this again but I only get the #N/A response.
Here's what my data looks like:
Col F Col G Col O
Unique ID Drug Choice
3.1 Heroin 2
3.2 Cocaine 4
3.3 Beer 0
4.1 Heroin 3
4.2 Cocaine 3
4.3 Beer 1
And herez the formula I am using
INDEX($O$2:$O$2277,MATCH($P$2,IF($F$2:$F$2277=Q2, $G$2:$G$2277),0))
where O2:O2277 is the col with the Choice values.
P 2 corresponds to Heroin that I have to look for in Col G and F column corresponds to the Unique ID.
View 3 Replies
View Related
Apr 21, 2014
I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.
Let's say I start with a table that looks like this:
A
B
C
D
Dept
E
Account
T
F
F2
G
Debit
Credit
Total
33010
[Code] ....
I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:
A
B
C
D
Dept
E
Account
[Code] .........
Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.
View 8 Replies
View Related
Mar 7, 2014
Alright, so I need data in column A(Last names) and data in column B(First name) to match their respective names on another sheet. When a match is made, it will return column C(Hours worked).
I originally used Vlookup to match last names, something akin to =VLOOKUP(A:A,Week1!A:P,4,FALSE)
But this does not account for employees that may have the same last names. Is there a way to combine two vlookup's? Or is Index a possible solution?
View 5 Replies
View Related
Feb 24, 2014
I need a vba code to return the value if two column's values are in sheet1 matching into sheet2,
Let us assume that, I have two sheets in a workbook(Sheet1 and Sheet2), In Sheet1, Col A and B are there some value and same like in Sheet2(Additionally Result in Col C)
I want to return the value in Col C of Sheet1(till A2 to last used rows) from Sheet2(Col C), if the first two column's values are matching with both sheets.
I have attached a sample workbook.
View 8 Replies
View Related
Oct 14, 2008
I have a large table of data:
A y y y y y y
x b b
x c c
x d
In the column headed 'A' I am wanting to return the value from the columns headed 'y'. There are 40+ columns and 1,500+ rows of data so I don't particularly want to do this manually (understandably).
Just for background the rows are lists of properties, the cells are populated with the name of a contractor, and the Ys represent different types of work.
View 9 Replies
View Related
Aug 4, 2006
I have two columns with the same data type (text) and I want to compare one column to another and if they match, return the data from another column.
For example: I have 2 columns of names on separate Excel files. One file contains name and phone numbers, while the other doesn't. I want to compare the names from file one with file two and if the names match, return the corresponding phone number in a new column.
I've tried VLOOKUP and IF statements, but I can't get it to come out.
View 4 Replies
View Related
Jun 1, 2007
I have a sheet with 2 big columns (3000 each). I want to search each element from the first column in the second column and put a message "Yes" if it is and "No" if it isn't, at the same position in the third column. How can i do that? I want to use the sheet for more than 3000 lines (about 5000). I attached an example.
View 9 Replies
View Related
Sep 21, 2013
I have two separate worksheets, and I am trying to create a Vlookup or Index and Match formula. Here is the example:
Sheet 1
Cell A1= Employee ID: 123-D.
Sheet2
Vlookup A1 from Sheet 1, and match the first five characters to Column A, Column I and Column P. If a match, return name (e.g. John Doe) in Sheet 1, cell B1.
View 9 Replies
View Related
Oct 4, 2008
1. i am comparing text
2. i have >100 countries, and with vlookup i have to type every single one, and this is what i want to escape (mb i need to refer to an array, or string, or no idea)
3. mb a solution could be sth with index, or match, i just tried it, copying some similar formula from www, but it didn't match the countries, and return the value respective for that country.
E.g. I have China in col1 (1st file), #employees 10 in col2 (1st file), and China in Col 1 in 2nd file. So, a formula should match China & China from both files, and return 10 as number of employees.
View 2 Replies
View Related
Dec 19, 2008
I have a spreadsheet with twenty columns. Column A has an item number (say "Clutch"), and the remainder of the columns have values. However, there only be one column in the range B:T which will have a value on the same row as "Clutch" (say "Black" in column "N").
How I can I return "Black" using a vlookup or should I be using something else?
View 4 Replies
View Related
Jan 10, 2014
I would like to return the value in column D (Store Name) that corresponds to the Max value in column N (Units Still Required). However, this Max value must meet certain criteria. That is, the State (column J) and Style Code (column Q) must be the same as that of the row being considered.
I have tried the below formula, and it appears to work the majority of the time, however, occassionally it does not adhere to the criteria (i.e. same State and Style Code).
For example in cell M7:
=IF(L7=0," ",INDEX(D$7:D$999,MATCH(MAX((IF((J:J=J7)*(Q:Q=Q7),N:N))),N$7:N$999,0)))
CTRL + SHIFT + ENTER
View 2 Replies
View Related
