Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
I have tjis problem that is killing me. I have this: DATE | USER | CAR ------------------ 5.1.2005 | MARK | LINCOLN 6.1.2005 | JOHN | LINCOLN 8.1.2005 | JOHN | LINCOLN 10.1.2005 | DAVID | LINCOLN 11.1.2005 | JIM | LINCOLN 10.1.2005 | DAVID | BMW 10.1.2005 | DAVID | MERCEDES How do i get the results regarding DAVID? Like this: 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES When i use Vlookup i get only the first result,or if i put true at the end of the Vlookup gormula it gives a wrong result. Please help me! -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
You may not need formulas to do what you want. Try a Pivot Table:
Select your data DavtaPivot Table Use: Excel List....Click Next Range: (already selected)...Click Next Click the Layout button ROW: Drag User, Date, and Car here (in that order) Double-click on each of those fields and set subtotals to None DATA: Drag Car here (it will become Count of Car) Click OK Select a destination for the Pivot Table Does that help? *********** Regards, Ron XL2002, WinXP-Pro "skarbanan" wrote: I have tjis problem that is killing me. I have this: DATE | USER | CAR ------------------ 5.1.2005 | MARK | LINCOLN 6.1.2005 | JOHN | LINCOLN 8.1.2005 | JOHN | LINCOLN 10.1.2005 | DAVID | LINCOLN 11.1.2005 | JIM | LINCOLN 10.1.2005 | DAVID | BMW 10.1.2005 | DAVID | MERCEDES How do i get the results regarding DAVID? Like this: 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES When i use Vlookup i get only the first result,or if i put true at the end of the Vlookup gormula it gives a wrong result. Please help me! -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Some VBA to do it
Sub Test() Dim iLastRow As Long Dim i As Long Dim rng As Range Columns("A:C").Sort key1:=Range("A1"), key2:=Range("B1"), header:=xlYes iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 3 Step -1 If Cells(i, "A").Value = Cells(i - 1, "A").Value And _ Cells(i, "B").Value = Cells(i - 1, "B").Value Then Cells(i, "C").Resize(1, 200).Copy Cells(i - 1, "D") If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If End If Next i If Not rng Is Nothing Then rng.Delete End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "skarbanan" wrote in message ... I have tjis problem that is killing me. I have this: DATE | USER | CAR ------------------ 5.1.2005 | MARK | LINCOLN 6.1.2005 | JOHN | LINCOLN 8.1.2005 | JOHN | LINCOLN 10.1.2005 | DAVID | LINCOLN 11.1.2005 | JIM | LINCOLN 10.1.2005 | DAVID | BMW 10.1.2005 | DAVID | MERCEDES How do i get the results regarding DAVID? Like this: 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES When i use Vlookup i get only the first result,or if i put true at the end of the Vlookup gormula it gives a wrong result. Please help me! -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
So you combine them if the date and the user are the same?
If yes, how about a macro? I sorted by Column C, too. Remove that portion if you don't want it. Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim RngToCopy As Range Set wks = Worksheets("sheet1") With wks FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("A1:C" & LastRow) .Sort key1:=.Columns(1), order1:=xlAscending, _ key2:=.Columns(2), order2:=xlAscending, _ key3:=.Columns(3), order3:=xlAscending, _ header:=xlYes End With For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow - 1, "A").Value = .Cells(iRow, "A").Value _ And .Cells(iRow - 1, "B").Value = .Cells(iRow, "B").Value Then Set RngToCopy = .Range(.Cells(iRow, "C"), _ .Cells(iRow, .Columns.Count).End(xlToLeft)) RngToCopy.Copy _ Destination:= .Cells(iRow - 1, .Columns.Count) _ .End(xlToLeft).Offset(0, 1) .Rows(iRow).Delete End If Next iRow End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm skarbanan wrote: I have tjis problem that is killing me. I have this: DATE | USER | CAR ------------------ 5.1.2005 | MARK | LINCOLN 6.1.2005 | JOHN | LINCOLN 8.1.2005 | JOHN | LINCOLN 10.1.2005 | DAVID | LINCOLN 11.1.2005 | JIM | LINCOLN 10.1.2005 | DAVID | BMW 10.1.2005 | DAVID | MERCEDES How do i get the results regarding DAVID? Like this: 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES When i use Vlookup i get only the first result,or if i put true at the end of the Vlookup gormula it gives a wrong result. Please help me! -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
I would use Data Filter AutoFilter to display all of those lines that
contained David......if you wanted to restrict the display to a certain date range, you can do that with the and < selections on the date column......or if you wanted only to see the Fords that David sold, it will show that as well........ Vaya con Dios, Chuck, CABGx3 "skarbanan" wrote in message ... I have tjis problem that is killing me. I have this: DATE | USER | CAR ------------------ 5.1.2005 | MARK | LINCOLN 6.1.2005 | JOHN | LINCOLN 8.1.2005 | JOHN | LINCOLN 10.1.2005 | DAVID | LINCOLN 11.1.2005 | JIM | LINCOLN 10.1.2005 | DAVID | BMW 10.1.2005 | DAVID | MERCEDES How do i get the results regarding DAVID? Like this: 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES When i use Vlookup i get only the first result,or if i put true at the end of the Vlookup gormula it gives a wrong result. Please help me! -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Here's a formula solution:
Data in A2:C8 E2 = 10.1.2005 F2 = David Enter this formula in G2 as an array using the key combo of CTRL,SHIFT,ENTER: =IF(SUMPRODUCT(--($A2:$A8=$E2),--($B2:$B8=$F2))=COLUMNS($A:A),INDEX($C2:$C8,SMALL( IF(($A2:$A8=$E2)*($B2:$B8=$F2),ROW(C2:C8)-ROW(C2)+1),COLUMNS($A:A))),"") Copy across until you get blanks. Biff "skarbanan" wrote in message ... I have tjis problem that is killing me. I have this: DATE | USER | CAR ------------------ 5.1.2005 | MARK | LINCOLN 6.1.2005 | JOHN | LINCOLN 8.1.2005 | JOHN | LINCOLN 10.1.2005 | DAVID | LINCOLN 11.1.2005 | JIM | LINCOLN 10.1.2005 | DAVID | BMW 10.1.2005 | DAVID | MERCEDES How do i get the results regarding DAVID? Like this: 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES When i use Vlookup i get only the first result,or if i put true at the end of the Vlookup gormula it gives a wrong result. Please help me! -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Well i'km doing this for a school assigment and the prof. said that we MUST use a formula and NO filter or pivot table. Now i will try the formula's submitted Thanks! -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
So you are cheating?
-- Regards, Peo Sjoblom (No private emails please) "skarbanan" wrote in message ... Well i'km doing this for a school assigment and the prof. said that we MUST use a formula and NO filter or pivot table. Now i will try the formula's submitted Thanks! -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
No i'm not cheating.It's not a shasme to ask for help, it's a shame to refuse to give help. I would like to see your face after weeks of working on assigments and not be able to do something more. I challenge you....why don't you try to solve it? hehe -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Does that mean you will credit Biff for helping you with this? If not it's
cheating unless you were told you could use any method including letting someone else doing it? -- Regards, Peo Sjoblom (No private emails please) "skarbanan" wrote in message ... No i'm not cheating.It's not a shasme to ask for help, it's a shame to refuse to give help. I would like to see your face after weeks of working on assigments and not be able to do something more. I challenge you....why don't you try to solve it? hehe -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Listen, i'm here to talk about excel, if you are not please do your posts elsewhere. -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
"skarbanan" wrote in message ... Listen, i'm here to talk about excel, if you are not please do your posts elsewhere. No, as Peo says, you are getting someone else to do your thinking for you, not talking about Excel. And you never answered Peo's question, will you credit Biff in your submission? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
if the person who helps me out with a piece of his coding wants to be crediti i will be happy to do it, i don't see the problem. But i doubt the someone will do it as i asked becasue i specified no pivot tabel no nothing but only excel database formula...so...i'll be glad to credit the person who achieves that. -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
But i doubt the someone will do it as i asked becasue i specified no
pivot tabel no nothing but only excel database formula...so...i'll be glad to credit the person who achieves that. Hmmm... The formula I suggested DOES just that. I *HATE* Pivot Tables so I use these types of formulas every day. They're really very simple once you understand how they work and you've written them by the thousands! Here's something to consider, however. If you're in school chances are pretty good that your instructor knows your capabilities. So, if you turn in an assignment that contains work that is above your capability, the instructor will know. Good luck! Biff "skarbanan" wrote in message ... if the person who helps me out with a piece of his coding wants to be crediti i will be happy to do it, i don't see the problem. But i doubt the someone will do it as i asked becasue i specified no pivot tabel no nothing but only excel database formula...so...i'll be glad to credit the person who achieves that. -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Our class has 220+ students. The prof. said that if we are curios that we should try solving the problem if not i could consign my workd as it is with one magazine found. Well, i tried yoiur formula but it gives me nothing as a result, i mean "" not 0 -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
If you're getting nothing but blanks then there's a problem with this
portion: =IF(SUMPRODUCT(--($A2:$A8=$E2),--($B2:$B8=$F2)) Can you post a sample file? Biff "skarbanan" wrote in message ... Our class has 220+ students. The prof. said that if we are curios that we should try solving the problem if not i could consign my workd as it is with one magazine found. Well, i tried yoiur formula but it gives me nothing as a result, i mean "" not 0 -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Yes...here is the sample... I got very frustrated because it seems a little problem but it turn put to be a bigger one http://www.excelforum.com/attachment...3&d=1135682721 or visit the first page of the thread -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Hi!
Here's your file: http://s44.yousendit.com/d.aspx?id=1...T3L017NLNF6RFY I added drop down lists so you can select the month, year and client name. I "fixed" the date column so that only the days of the month that is selected will be displayed. I created an additional named range, Magazine. That should be self explanatory! I changed the Total formula. Biff "skarbanan" wrote in message ... Yes...here is the sample... I got very frustrated because it seems a little problem but it turn put to be a bigger one http://www.excelforum.com/attachment...3&d=1135682721 or visit the first page of the thread -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Thank you biff. You did a great job.I odn't know how to thank you. I have a last request....could you explain me this forumula ? {=IF(SUMPRODUCT(--(Dates=$B10);--(nomi=$B$6))=COLUMNS($A:A);INDEX(Magazine;SMALL(I F((Dates=$B10)*(nomi=$B$6);ROW(Magazine)-ROW(C$4)+1);COLUMNS($A:A)));"")} i mean i understand the functions...but i never used this signs "--" and "{}" when i click on the fomula the "{}" dissapear end another value comes out... This is what i don't understand.... Biff thanks again! -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Hi!
For detailed info about the "--", see: http://xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html The squiggly brackets: { } mean that the formula is an array formula. An array formula operates on more than one element at the same time. Array formulas are handled in a special way. Normally, when you enter a formula like: =SUM(A1:A10) You type the formula in the cell then you hit the ENTER key. Array formulas are different. You type the formula in the cell then instead of just hitting the ENTER key you need to use a combination of keys. You MUST hold down the CTRL key AND the SHIFT key then hit ENTER. So, the combination is CTRL,SHIFT,ENTER. When done properly Excel will place the squiggly brackets: { } around the formula. You can't just type the brackets. You MUST use the key combo. This also applies whenever you edit an array formula for any reason. You MUST re-enter it as an array using the key combo. Biff "skarbanan" wrote in message ... Thank you biff. You did a great job.I odn't know how to thank you. I have a last request....could you explain me this forumula ? {=IF(SUMPRODUCT(--(Dates=$B10);--(nomi=$B$6))=COLUMNS($A:A);INDEX(Magazine;SMALL(I F((Dates=$B10)*(nomi=$B$6);ROW(Magazine)-ROW(C$4)+1);COLUMNS($A:A)));"")} i mean i understand the functions...but i never used this signs "--" and "{}" when i click on the fomula the "{}" dissapear end another value comes out... This is what i don't understand.... Biff thanks again! -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Thanks Biff. I read the information about "--" signs and i understand it know. I understood your code only not this part : IF((Dates=$B13)*(nomi=$B$6);ROW(Magazine)-ROW(C$4)+1);2));"") Well the ROW(Magazine)-ROW(C$4)+1);2) are giving this result 1 Row(Magazine)=4 -ROW(C$4)+1)= c4=4 + 1 = 4 so ROW(Magazine)-ROW(C$4)+1);2)) equals 1, but when i put the number 1 instead of the formula it gives another value...Now i'm asking:is it because it's an array formula and the searching of the data stops at every row to continue from the last row or it's somethin else? Just for curiosty and to understan the formula i triey to put ROW(B2)+1 (which is the same as the next one) instead of ROW(C$4)+1 but it gives me a wrong result... could you explain please? I't that i'm really into it and i would like to learn... p.s sorry for being boring -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
What this does is create an array of row offsets for all rows that meet the
two conditions Dates=$B13 and nomi=$B$6 If you select this part ROW(Magazine)-ROW(C$4)+1 in the formula bar, and press F9, you will see the row number offsets evaluated (1;2;3;...). If you then select IF((Dates=$B10)*(nomi=$B$6),ROW(Magazine)-ROW(C$4)+1) and F9, you will see only row number offsets of matching items (1;2;FALSE;FALSE;...) This array is then used by the SMALL function using the column number as the position to get a valid row offset, which is in turn passed to the INDEX function to get an actual value. -- HTH RP (remove nothere from the email address if mailing direct) "skarbanan" wrote in message ... Thanks Biff. I read the information about "--" signs and i understand it know. I understood your code only not this part : IF((Dates=$B13)*(nomi=$B$6);ROW(Magazine)-ROW(C$4)+1);2));"") Well the ROW(Magazine)-ROW(C$4)+1);2) are giving this result 1 Row(Magazine)=4 -ROW(C$4)+1)= c4=4 + 1 = 4 so ROW(Magazine)-ROW(C$4)+1);2)) equals 1, but when i put the number 1 instead of the formula it gives another value...Now i'm asking:is it because it's an array formula and the searching of the data stops at every row to continue from the last row or it's somethin else? Just for curiosty and to understan the formula i triey to put ROW(B2)+1 (which is the same as the next one) instead of ROW(C$4)+1 but it gives me a wrong result... could you explain please? I't that i'm really into it and i would like to learn... p.s sorry for being boring -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
In addition to Bob's explanation......
When we use the Index function the values that are indexed are stored in a specific order or position. Consider this example that's in the range of A10:A14: A10 = 1/1/1997 A11 = 1/1/1997 A12 = 1/5/1997 A13 = 1/7/1997 A14 = 1/8/1997 When we index this range: =INDEX(A10:A14,..............) The value of A10 is in the 1st position The value of A11 is in the 2nd position The value of A12 is in the 3rd position The value of A13 is in the 4th position The value of A14 is in the 5th position We use the ROW(......) function (in the type of formula that you're using) as a means of generating an array of numbers that correspond to the Indexed positions. So, using: =INDEX(A10:A14,..............) Which holds 5 values, we need to generate an array of numbers from 1 to 5. So, we could use ROW(1:5). But using ROW(1:5) means that you would have to know the exact number of values indexed. You could manually count them but that's not very "sophisticated". So, as Bob explained, we can use the actual range reference, A10:A14, and subtract the offset.. If we used ROW(A10:A14) that would generate an array of numbers from 10 to 14 and since we don't have positions 10 to 14 in the Index function that would return a formula error. So, we use ROW(A10:A14)-ROW(A10)+1, which evaluates to this: 10-10+1 = 1 11-10+1 = 2 12-10+1 = 3 13-10+1 = 4 14-10+1 = 5 Now we have our array of numbers from 1 to 5 that correspond to the 5 positions of the indexed values. Now, let's combine that with a conditional test: Assume A1 = 1/2/1997 Our conditional test could be something like this: IF(A10:A14<A1,ROW(A10:A14)-ROW(A10)+1) The conditional test: A10:A14<A1, will return an array of TRUE or FALSE: TRUE TRUE FALSE FALSE FALSE Now, the IF function will return the value_if_TRUE as the calculated ROW number and the value_if_FALSE as FALSE. It will look like this: 1 2 FALSE FALSE FALSE Then, as Bob explained, that array will be passed to the SMALL function: SMALL({1;2;FALSE;FALSE;FALSE},COLUMNS($A:A)) And in turn, that value will be passed to the INDEX function. Biff "Bob Phillips" wrote in message ... What this does is create an array of row offsets for all rows that meet the two conditions Dates=$B13 and nomi=$B$6 If you select this part ROW(Magazine)-ROW(C$4)+1 in the formula bar, and press F9, you will see the row number offsets evaluated (1;2;3;...). If you then select IF((Dates=$B10)*(nomi=$B$6),ROW(Magazine)-ROW(C$4)+1) and F9, you will see only row number offsets of matching items (1;2;FALSE;FALSE;...) This array is then used by the SMALL function using the column number as the position to get a valid row offset, which is in turn passed to the INDEX function to get an actual value. -- HTH RP (remove nothere from the email address if mailing direct) "skarbanan" wrote in message ... Thanks Biff. I read the information about "--" signs and i understand it know. I understood your code only not this part : IF((Dates=$B13)*(nomi=$B$6);ROW(Magazine)-ROW(C$4)+1);2));"") Well the ROW(Magazine)-ROW(C$4)+1);2) are giving this result 1 Row(Magazine)=4 -ROW(C$4)+1)= c4=4 + 1 = 4 so ROW(Magazine)-ROW(C$4)+1);2)) equals 1, but when i put the number 1 instead of the formula it gives another value...Now i'm asking:is it because it's an array formula and the searching of the data stops at every row to continue from the last row or it's somethin else? Just for curiosty and to understan the formula i triey to put ROW(B2)+1 (which is the same as the next one) instead of ROW(C$4)+1 but it gives me a wrong result... could you explain please? I't that i'm really into it and i would like to learn... p.s sorry for being boring -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Urgent help needed!
Just a thought:
These types of formulas are very powerful and useful. If you can learn to master them you'll be way ahead of your peers. Biff "Biff" wrote in message ... In addition to Bob's explanation...... When we use the Index function the values that are indexed are stored in a specific order or position. Consider this example that's in the range of A10:A14: A10 = 1/1/1997 A11 = 1/1/1997 A12 = 1/5/1997 A13 = 1/7/1997 A14 = 1/8/1997 When we index this range: =INDEX(A10:A14,..............) The value of A10 is in the 1st position The value of A11 is in the 2nd position The value of A12 is in the 3rd position The value of A13 is in the 4th position The value of A14 is in the 5th position We use the ROW(......) function (in the type of formula that you're using) as a means of generating an array of numbers that correspond to the Indexed positions. So, using: =INDEX(A10:A14,..............) Which holds 5 values, we need to generate an array of numbers from 1 to 5. So, we could use ROW(1:5). But using ROW(1:5) means that you would have to know the exact number of values indexed. You could manually count them but that's not very "sophisticated". So, as Bob explained, we can use the actual range reference, A10:A14, and subtract the offset.. If we used ROW(A10:A14) that would generate an array of numbers from 10 to 14 and since we don't have positions 10 to 14 in the Index function that would return a formula error. So, we use ROW(A10:A14)-ROW(A10)+1, which evaluates to this: 10-10+1 = 1 11-10+1 = 2 12-10+1 = 3 13-10+1 = 4 14-10+1 = 5 Now we have our array of numbers from 1 to 5 that correspond to the 5 positions of the indexed values. Now, let's combine that with a conditional test: Assume A1 = 1/2/1997 Our conditional test could be something like this: IF(A10:A14<A1,ROW(A10:A14)-ROW(A10)+1) The conditional test: A10:A14<A1, will return an array of TRUE or FALSE: TRUE TRUE FALSE FALSE FALSE Now, the IF function will return the value_if_TRUE as the calculated ROW number and the value_if_FALSE as FALSE. It will look like this: 1 2 FALSE FALSE FALSE Then, as Bob explained, that array will be passed to the SMALL function: SMALL({1;2;FALSE;FALSE;FALSE},COLUMNS($A:A)) And in turn, that value will be passed to the INDEX function. Biff "Bob Phillips" wrote in message ... What this does is create an array of row offsets for all rows that meet the two conditions Dates=$B13 and nomi=$B$6 If you select this part ROW(Magazine)-ROW(C$4)+1 in the formula bar, and press F9, you will see the row number offsets evaluated (1;2;3;...). If you then select IF((Dates=$B10)*(nomi=$B$6),ROW(Magazine)-ROW(C$4)+1) and F9, you will see only row number offsets of matching items (1;2;FALSE;FALSE;...) This array is then used by the SMALL function using the column number as the position to get a valid row offset, which is in turn passed to the INDEX function to get an actual value. -- HTH RP (remove nothere from the email address if mailing direct) "skarbanan" wrote in message ... Thanks Biff. I read the information about "--" signs and i understand it know. I understood your code only not this part : IF((Dates=$B13)*(nomi=$B$6);ROW(Magazine)-ROW(C$4)+1);2));"") Well the ROW(Magazine)-ROW(C$4)+1);2) are giving this result 1 Row(Magazine)=4 -ROW(C$4)+1)= c4=4 + 1 = 4 so ROW(Magazine)-ROW(C$4)+1);2)) equals 1, but when i put the number 1 instead of the formula it gives another value...Now i'm asking:is it because it's an array formula and the searching of the data stops at every row to continue from the last row or it's somethin else? Just for curiosty and to understan the formula i triey to put ROW(B2)+1 (which is the same as the next one) instead of ROW(C$4)+1 but it gives me a wrong result... could you explain please? I't that i'm really into it and i would like to learn... p.s sorry for being boring -- skarbanan ------------------------------------------------------------------------ skarbanan's Profile: http://www.excelforum.com/member.php...o&userid=29901 View this thread: http://www.excelforum.com/showthread...hreadid=496084 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent help needed - floating segmented bar chart | Charts and Charting in Excel | |||
Urgent Help needed - I need to stop a process that is running forever in Excel | Excel Discussion (Misc queries) | |||
Urgent Help Needed with coloumn compare | Excel Discussion (Misc queries) | |||
Help, Excel Formula Needed -- Urgent | Excel Worksheet Functions | |||
Urgent help needed: IF function | Excel Worksheet Functions |