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!

You may not need formulas to do what you want. Try a Pivot Table:

Select your data
Davta>Pivot 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
>
>

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" <skarbanan.20o7wa_1135640101.0952@excelforum-nospam.com> wrote
in message news:skarbanan.20o7wa_1135640101.0952@excelforum-nospam.com...
>
> 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
>

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

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" <skarbanan.20o7wa_1135640101.0952@excelforum-nospam.com> wrote
in message news:skarbanan.20o7wa_1135640101.0952@excelforum-nospam.com...
>
> 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
>

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" <skarbanan.20o7wa_1135640101.0952@excelforum-nospam.com> wrote
in message news:skarbanan.20o7wa_1135640101.0952@excelforum-nospam.com...
>
> 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
>

So you are cheating?

--
Regards,

Peo Sjoblom

(No private emails please)


"skarbanan" <skarbanan.20p42m_1135681803.283@excelforum-nospam.com> wrote in
message news:skarbanan.20p42m_1135681803.283@excelforum-nospam.com...
>
> 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
>

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

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" <skarbanan.20pl7a_1135704002.0264@excelforum-nospam.com> wrote
in message news:skarbanan.20pl7a_1135704002.0264@excelforum-nospam.com...
>
> 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
>

Listen, i'm here to talk about excel, if you are not please do your posts elsewhere.

"skarbanan" <skarbanan.20pp4z_1135709103.6556@excelforum-nospam.com> wrote
in message news:skarbanan.20pp4z_1135709103.6556@excelforum-nospam.com...
>
> 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?

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.

>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" <skarbanan.20pxpa_1135720201.8097@excelforum-nospam.com> wrote
in message news:skarbanan.20pxpa_1135720201.8097@excelforum-nospam.com...
>
> 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
>

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

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" <skarbanan.20r3lm_1135774502.8132@excelforum-nospam.com> wrote
in message news:skarbanan.20r3lm_1135774502.8132@excelforum-nospam.com...
>
> 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
>

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

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" <skarbanan.20rrfy_1135805402.763@excelforum-nospam.com> wrote in
message news:skarbanan.20rrfy_1135805402.763@excelforum-nospam.com...
>
> 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
>

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" <skarbanan.20subz_1135855802.6513@excelforum-nospam.com> wrote
in message news:skarbanan.20subz_1135855802.6513@excelforum-nospam.com...
>
> 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(IF((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
>

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

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" <skarbanan.20tufy_1135902602.1857@excelforum-nospam.com> wrote
in message news:skarbanan.20tufy_1135902602.1857@excelforum-nospam.com...
>
> 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
>

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23Fn98lUDGHA.748@TK2MSFTNGP12.phx.gbl...
> 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" <skarbanan.20tufy_1135902602.1857@excelforum-nospam.com> wrote
> in message news:skarbanan.20tufy_1135902602.1857@excelforum-nospam.com...
>>
>> 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
>>
>
>

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" <biffinpitt@comcast.net> wrote in message
news:%238DfIEZDGHA.984@tk2msftngp13.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23Fn98lUDGHA.748@TK2MSFTNGP12.phx.gbl...
>> 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" <skarbanan.20tufy_1135902602.1857@excelforum-nospam.com>
>> wrote
>> in message news:skarbanan.20tufy_1135902602.1857@excelforum-nospam.com...
>>>
>>> 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
>>>
>>
>>
>
>