I have one sheet that contains data about products, the product number, the class it belongs to, and many other bits. The data is sorted by product number.
In a second sheet I have a list of classes in column G, I'd like to return a list of product numbers in columns H to wherever is needed.
For example there might be two products in class one, class one is in G1, with the first product number in H1 and the second in H2.
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
I've tried to multiply each element in a 6x6 array by a similar 6x6 array, both on the same sheet, and it worked.(see Macro2 and attached xls file "Test").Then I got more ambitious and tried to do the multiplication from a standard array in sheet "TestA", with the result on the same sheet, by each array in sheet "TestB" and failed.How do I solve this problem? Pgualb PS:I'm using the R1C1 style.
Sub Macro2()
For y = 29 To 34
For x = 2 To 7
Cells(x, y) = Cells(x, y - 27) * Cells(x, y - 18)
Next x
Next y
End Sub
Sub Teste12()
'Multiplica matriz em TestB por matriz padrão em TestA com _
'resultado na matriz em TestA correspondente à matriz em TestB
'
Dim x, y As Integer
For y = 2 To 7.............
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.
I have created a sheet that contains a new diet program, calculated down to the precise calorie required for my training routine. Please see below for an example of one of my daily meals:
[url]
I need a formula that will help me to create a weekly shopping list (as the values in the example above will change on a regular basis). So I need to take all values from the from columns A & B, multiply them by 5 and then show me the totals in another sheet.
Similarly, I need to do the same with columns E & F, only they need to be multiplied by 2. The totals then need to be added to give me the required amount (in grams) for the week.
I have attached the file, in the last sheet (Consolidated) i need to get the data from all the previous sheets, my problem is that whenever i have similar cells the data that is showing is only the first value and not the sum of all values,
View 1 Replies View RelatedI would like to sum the values of a range using something similar to vlookup. The problem with using vlookup is that vlookup returns only the first instance. I have multiple values therefore vlookup would not work. I have 2 variables which is causing my frustration. I have added an example to explain. On the column where it says "correct answer" is where the formula should go. I displayed what the formula should produce so that it explains what I want as the output. The "static column" which contains variables 1 and 2 doesn't change.
The two variables it needs to search for is shown in the variable 1 and variable 2 columns. And the lookup range is on the left.
So basically it needs to match the two variables in the range and then grab the sum of ALL quantities where the two variables match . quantity amount is found on the leftmost column.
Is this possible. sumif works but only with 1 variable.
LOOKUP RANGE
formula column
static column
static column
formula column
static column
static column
quantity
variable 1
variable 2
correct answer
variable 1
variable 2
[Code] ......
I need a formula that looks at 2 columns and if the data is not similar to return the value.
For example
Column A
joe Bloggs
Column B
Bloggs, joe
Column C
if value in column B is not similar to column A to return value of column B
i am looking to create a vlookup but with the ability to easily change the column number index so that i can use different columns.
As an example. In a worksheet i have a table with the names of cars in column A starting row 3. Column B to m Row 1 is headed Jan to dec, row 2 same columns is a country name eg UK. Column N to Y row 1 is Jan to Dec again and row 2 of these columns is a diff country say Germany. This repeats for a few more countries. The data within Row 4 for these columns i.e per car is all prices. The table therefore shows the prices of cars per country per month.
I then have a seperate worksheet for each country where the cars are again listed in column A and Jan to Dec is in column b to M but the data is hard coded being the number of cars. I would like to use column N to link to 1 of these months hard coded counts dependant on what month i decide to forecast on. The easy way being that if i wanted to use Jan count number i would link the count for that car type to =b4 etc. Is there an easy way to allow me to change the link should i decide i want Feb ?
The second question is within each countries worksheet i want to bring into column p the countries related car price for a month i select. It may be that the count number differs from the price i select.
I am creating a tracking spreadsheet where i can toggle between months (attached). It returns data properly when I select January or February from the yellow highlighted dropdown list. However, when i choose March, it returns 1/0/1900 because no data has been entered for March (table array) on the AAAG tab. How do I write the formula to return a blank cell on the summary sheet when no information has been entered into the table array yet?
View 2 Replies View RelatedI am trying to create a macro, that ultimately does the reverse of one written a while back.
I have created a macro that exports "Roster_Data" to "envision_Roster" in .csv style formatting. What I want to achieve is reverse engineer the macro to return the data back to a similar state.
I am trying to transpose column D into rows that correspond with dates in column c in a sheet called "OutputView" this in essence is similar if not exactly the same as the original worksheet "Roster_Data"
I have split the macro I am working on into three separate modules.
Module 1 - Initial Export of "Roster_Data" to "envision_Roster"
Module 2 - Format and output worksheet to find MAX date and MIN date and produce top rows of data
Module 3 - Analyse, Undertake Logical Tests of data, and transpose to suit (Replicate the initial "Roster_Data" view)
I'm having trouble visioning this altogether so I've been starting with basic code to output basic stuff, but I still can't relate this back to my data.
[Code]....
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
I'm trying to use an array to carry out string function on a range of excel cells.
Here is the code I am using
Dim arrXl As Variant
arrXl = ws.Range("F1:F" & ws.Range("D1").End(xlDown).Row)
For i = LBound(arrXl) To UBound(arrXl)
MsgBox (LBound(arrXl))
'If 1st char is different from 3rd char then remove all of string after 1st char
If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then
arrXl(i) = Left(arrXl(i), 1)
End If
'If 1st char is different from 7th char then delete string after 5th char
If Left(arrXl(i), 1) <> Mid(arrXl(i), 7, 1) Then
arrXl(i) = Left(arrXl(i), 5)
End If
The lbound function returns the value of 1 as the lower bound, I do not have "Option base 1" set so I was expecting the lbound value to be 0. The first 2 cells in the F column are blank so this may have something to do with it, I am unsure if cells in excel can be null if they can be null one cell may be null and the other may be a zero length string but I am unsure about this.
The ubound function returns a value of 487.
The code breaks when I try to access an element in the array so it breaks on the line:
If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then
and returns the "Subscript out of range" error message.
I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.
View 3 Replies View Relatedhow to use Array formulas in Excel. I can Sum, Count
etc. I am wondering if you can return an array of data with an array formula
Example:
Name Replied?
Bob N
Brian Y
Robyn N
Rachel Y
From the table above if I test for "N" I'd like the formula to return a list
of names, in this case Bob and Robyn.
Basically have a spreadsheet to track an athletic competition going of for the purposes of a fantasy game (like fantasy football). The scores from each event are being copied and pasted into a data pages and then other pages pull from that for calculations. I'm using rankings (rank.eq equation) on a calculation tab, and then using those rankings on a leader-board tab find placement via the VLookup function. The issue I'm running into is ties, when two people are ranked the same. I've been playing with this:
=IF(ISERR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE)),VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),
VLOOKUP($J16,INDIRECT("Men!A" & LOOKUP(J16,Men!A1:A43)+1):$G$43,7,FALSE))
Where it checks for an error in the Vlookup, if its not an error then it does the VLookup, if it is then if looks up the previous ranking and the VLookup array uses Lookup to find the position of the last rank, increments it by one and starts the new Vlookup there.
I have the following information (small sample only):
CodeItemList
162AVS0001 AVS0001
162AVS0002 AVS0003
162AVS0003 BUD0002
162AVS0005C5O1208
102BRE234CAL0007
102BRE-508EQU0003
102BRE-AB09 FXUK21916/2
162BUD0002GMC0003
162C5ENV-TNT HSS0001
162C5O1208 HSS0006
162CAL0005 IBM0003
162CAL0007 KUN0002
The list on the right is derived from the following array formula which has been copied down the column to end of the data range:
{=VLOOKUP($G$130, INDEX(G133:I252, SMALL(IF($G$130=INDEX(G133:I252, , 1), ROW(G133:I252)-MIN(ROW(G133:I252))+1, ""), ROW(1:1)), , 1), 2, FALSE)}
$G$130 refers to a value in that cell which is user defined from a drop down list and in this instance, the value is 162. Therefore, I would expect to see all of the items that contain 162, under the list column. As you can see, it has not returned all of the values relevant to 162. It has missed AVS002, AVS005 C5 ENV and CAL0005 in this example. The ommissions seem random
my data is formed of 2 columns column 1 (currency) & column 2 (balance)
i am writing an array formula using the vlookup function, the formula enables me to add all balances after converting them to $. i dont know whats wrong with my formula
please find attached an example of ....
I am getting the hang of this formula array stuff but I have another issue. I have gotten this to work in the actual Spreadsheet but when I try to put that same string into Code I get an error. I am trying to add a Vlookup into an Formula Array. I am building a new sheet and adding this to the sheet to do the calculations. I was able to get the code to work fine until I added the Vlookup section. I am doing this in small sections to make sure each part works. I am getting "unable to set the FormulaArray property of the Range Class" error message.
NewSheet.Cells(CurrRow, 4).FormulaArray = "=IF(ISNA(INDEX(FY08Rates!A:F,MATCH(LEFT(A" & CurrRow & ",SEARCH(""-"",A" & CurrRow & ")-2)&Vlookup(B" & CurrRow & ",FY08StdBillingRoles!A:B,2,False),FY08Rates!A:A&FY08Rates!B:B,0),3)),0,INDEX(FY08Rates!A:F,MATCH(LEFT(A" & CurrRow & ",SEARCH(""-"",A" & CurrRow & ")-2)&Vlookup(B" & CurrRow & ",FY08StdBillingRoles!A:B,2,False),FY08Rates!A:A&FY08Rates!B:B,0),3))"
I want to find a number in my array and return the value in column A. Trouble is if the array goes past column R I get the wrong Answer. the formula I am using is
=LOOKUP($C$22,$B$2:$AC$18,$A$2:$A$18)
Some cells are blank I am trying to draw a raffle.
Here is my data and formulas below:
The lookup of value 209 produces correct results when I look up as far as column R after that I get the wrong answer.
A
B
C
D
E
F
G
[Code] ....
The short version:
(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25))) evaluates out to series of 340 True and Falses. I need to be able to pull the nth True, and then somehow index that against AH2:341.
I'm working with a record of 53,000 entries with latitudes and longitudes and I need to cross compare them with 341 locations in the United States.
I've already done some of the heavy lifting, which supported narrow an initial list of 85,000 records to at least recognize which records where within any of the 341 points. (Less arrays makes my computer happy.)
This formula gives me the count of locations each entry is within one of the 341:
=SUM(IF(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25,1,0)) {CSE} & copied down 53k times
The actual return range is AH2:AH341 for the location number.
Now, I need to be able to list each of the 341 they are close to. The max count is 17, so I'll only need to list out 17 columns.
Maybe something like =LOOKUP(1,1/(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25))),$AH$2:$AH$341)
but this only returns the first match. How do I return the 2nd, 3rd, 4th, etc?
Because of the sensitive nature of my data, we can use a much simpler array which I'll adapt; I'm mostly trying to understand the logic or formula that should be used.
Return Nth Match.xlsx
I have the attached example workbook and I'm trying to lookup a date from a list based on a project start month:
- in cell D8 I want to be able to return the date that corresponds to the project month in row 5
- the dates are in column A
Example WB 1.xlsx‎
My first attempt is to return a simple Array of ColorIndexes.
View 10 Replies View RelatedI have an array with a lot of rows and columns filled with different codes.
I need to look into this array for one specific number and return the value in the same row in an specific column.
I can't use Vlookup in this case because it needs to have the values you are looking for in the first column and my values are spread in different columns.
I have two columns of data, one containing Groups, another containing Items. Like this:
Group | Item
A | Apple
A | Orange
B | Pear
B | Banana
C | Tire
D | Coffee
etc.
On another sheet, I have each of the groups listed as column headings. I want to resort the data and display it in columns instead of in a single list.
I can write a VLOOKUP to search for "B" for example, and return "Pear", but I want to return all matches for "B" not just the first one.
I have a spreadsheet of information (product codes and values) and want to use VBA to find the top ten list of products by value. The list of products is variable in length so I can pass the data to a two-dimensioned array.
How do I ask VBA to return the top ten products by value - there can be duplicate values so I just want ten (if there are 4 #9s, I just want two of them, any two)?
I have a spreadsheet with 51 worksheets - a "Master" worksheet and 50 datasheets (labelled 1-50). Within the "Master" worksheet the first column has numerical values ranging from 1-50.
One of the columns in the "Master" worksheet is a VLOOKUP that is trying to pull data, of which the table array is dependent upon the value in the first column. For example:
If cell A2 has the number 1 the VLOOKUP would be:
=VLOOKUP(E2,'1'!$A:$E,3,0)
If cell A2 had the number 2 the VLOOKUP would be:
=VLOOKUP(E2,'2'!$A:$E,3,0)
If cell A2 had the number 3 the VLOOKUP would be:
=VLOOKUP(E2,'3'!$A:$E,3,0)
etc.
Tried nesting an INDIRECT function with the VLOOKUP but it didn't work.
In sheet1 I have name(column A) and value(column B)
In sheet2 I have name (column C) and key(column D)
In sheet 3 I have results
Example ( It is just a total nonsense example, the real data is net inflow of some funds that are unique)
Sheet1
House 1000
Car 1500
pet 2000
Sheet 2
house1
car1
pet2
So in the sheet 3 I want to put a formula that is capable to sum all values of the sheet2 if the name has a key of 1in the sheet2, the key columns has values of 1 or 2
I think in SQL will be something with join and group by with having clause.
The result will be 2500 in the sheet 3.
What I tried: My attempt is to do something like this code ( using array functions).