Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
skarbanan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
skarbanan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
skarbanan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
skarbanan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
skarbanan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
skarbanan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
skarbanan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
skarbanan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
skarbanan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Urgent help needed - floating segmented bar chart Jeff B Charts and Charting in Excel 1 October 14th 05 07:21 AM
Urgent Help needed - I need to stop a process that is running forever in Excel shadestreet Excel Discussion (Misc queries) 2 October 6th 05 09:59 PM
Urgent Help Needed with coloumn compare Brento Excel Discussion (Misc queries) 1 July 29th 05 12:35 AM
Help, Excel Formula Needed -- Urgent Urgent Excel Worksheet Functions 2 December 14th 04 11:32 PM
Urgent help needed: IF function Terence Excel Worksheet Functions 3 November 16th 04 03:29 AM


All times are GMT +1. The time now is 01:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"