software

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 | MERCEDESWhen 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: www.excelforum.com/member.php...oamp;userid=29901
View this thread: www.excelforum.com/showthread...hreadid=496084You may not need formulas to do what you want. Try a Pivot Table:

Select your data
Davtagt;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-Proquot;skarbananquot; wrote:

gt;
gt; I have tjis problem that is killing me.
gt; I have this:
gt;
gt; DATE | USER | CAR
gt; ------------------
gt; 5.1.2005 | MARK | LINCOLN
gt; 6.1.2005 | JOHN | LINCOLN
gt; 8.1.2005 | JOHN | LINCOLN
gt; 10.1.2005 | DAVID | LINCOLN
gt; 11.1.2005 | JIM | LINCOLN
gt; 10.1.2005 | DAVID | BMW
gt; 10.1.2005 | DAVID | MERCEDES
gt;
gt; How do i get the results regarding DAVID?
gt; Like this:
gt;
gt; 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES
gt;
gt;
gt; When i use Vlookup i get only the first result,or if i put true at the
gt; end of the Vlookup gormula it gives a wrong result.
gt; Please help me!
gt;
gt;
gt; --
gt; skarbanan
gt; ------------------------------------------------------------------------
gt; skarbanan's Profile: www.excelforum.com/member.php...oamp;userid=29901
gt; View this thread: www.excelforum.com/showthread...hreadid=496084
gt;
gt;

Some VBA to do it

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range

Columns(quot;A:Cquot;).Sort key1:=Range(quot;A1quot;), key2:=Range(quot;B1quot;), header:=xlYes
iLastRow = Cells(Rows.Count, quot;Aquot;).End(xlUp).Row
For i = iLastRow To 3 Step -1
If Cells(i, quot;Aquot;).Value = Cells(i - 1, quot;Aquot;).Value And _
Cells(i, quot;Bquot;).Value = Cells(i - 1, quot;Bquot;).Value Then
Cells(i, quot;Cquot;).Resize(1, 200).Copy Cells(i - 1, quot;Dquot;)
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)quot;skarbananquot; gt; wrote
in message ...
gt;
gt; I have tjis problem that is killing me.
gt; I have this:
gt;
gt; DATE | USER | CAR
gt; ------------------
gt; 5.1.2005 | MARK | LINCOLN
gt; 6.1.2005 | JOHN | LINCOLN
gt; 8.1.2005 | JOHN | LINCOLN
gt; 10.1.2005 | DAVID | LINCOLN
gt; 11.1.2005 | JIM | LINCOLN
gt; 10.1.2005 | DAVID | BMW
gt; 10.1.2005 | DAVID | MERCEDES
gt;
gt; How do i get the results regarding DAVID?
gt; Like this:
gt;
gt; 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES
gt;
gt;
gt; When i use Vlookup i get only the first result,or if i put true at the
gt; end of the Vlookup gormula it gives a wrong result.
gt; Please help me!
gt;
gt;
gt; --
gt; skarbanan
gt; ------------------------------------------------------------------------
gt; skarbanan's Profile:
www.excelforum.com/member.php...oamp;userid=29901
gt; View this thread: www.excelforum.com/showthread...hreadid=496084
gt;
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(quot;sheet1quot;)

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row

With .Range(quot;A1:Cquot; amp; 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, quot;Aquot;).Value = .Cells(iRow, quot;Aquot;).Value _
And .Cells(iRow - 1, quot;Bquot;).Value = .Cells(iRow, quot;Bquot;).Value Then
Set RngToCopy = .Range(.Cells(iRow, quot;Cquot;), _
.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:
www.mvps.org/dmcritchie/excel/getstarted.htm

skarbanan wrote:
gt;
gt; I have tjis problem that is killing me.
gt; I have this:
gt;
gt; DATE | USER | CAR
gt; ------------------
gt; 5.1.2005 | MARK | LINCOLN
gt; 6.1.2005 | JOHN | LINCOLN
gt; 8.1.2005 | JOHN | LINCOLN
gt; 10.1.2005 | DAVID | LINCOLN
gt; 11.1.2005 | JIM | LINCOLN
gt; 10.1.2005 | DAVID | BMW
gt; 10.1.2005 | DAVID | MERCEDES
gt;
gt; How do i get the results regarding DAVID?
gt; Like this:
gt;
gt; 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES
gt;
gt; When i use Vlookup i get only the first result,or if i put true at the
gt; end of the Vlookup gormula it gives a wrong result.
gt; Please help me!
gt;
gt; --
gt; skarbanan
gt; ------------------------------------------------------------------------
gt; skarbanan's Profile: www.excelforum.com/member.php...oamp;userid=29901
gt; View this thread: www.excelforum.com/showthread...hreadid=496084

--

Dave Peterson

I would use Data gt; Filter gt; 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 gt; and lt; 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, CABGx3quot;skarbananquot; gt; wrote
in message ...
gt;
gt; I have tjis problem that is killing me.
gt; I have this:
gt;
gt; DATE | USER | CAR
gt; ------------------
gt; 5.1.2005 | MARK | LINCOLN
gt; 6.1.2005 | JOHN | LINCOLN
gt; 8.1.2005 | JOHN | LINCOLN
gt; 10.1.2005 | DAVID | LINCOLN
gt; 11.1.2005 | JIM | LINCOLN
gt; 10.1.2005 | DAVID | BMW
gt; 10.1.2005 | DAVID | MERCEDES
gt;
gt; How do i get the results regarding DAVID?
gt; Like this:
gt;
gt; 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES
gt;
gt;
gt; When i use Vlookup i get only the first result,or if i put true at the
gt; end of the Vlookup gormula it gives a wrong result.
gt; Please help me!
gt;
gt;
gt; --
gt; skarbanan
gt; ------------------------------------------------------------------------
gt; skarbanan's Profile:
www.excelforum.com/member.php...oamp;userid=29901
gt; View this thread: www.excelforum.com/showthread...hreadid=496084
gt;
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))gt;=COLUMNS($A:A),INDEX($C2:$C8,SMALL( IF(($A2:$A8=$E2)*($B2:$B8=$F2),ROW(C2:C8)-ROW(C2) 1),COLUMNS($A:A))),quot;quot;)

Copy across until you get blanks.

Biff

quot;skarbananquot; gt; wrote
in message ...
gt;
gt; I have tjis problem that is killing me.
gt; I have this:
gt;
gt; DATE | USER | CAR
gt; ------------------
gt; 5.1.2005 | MARK | LINCOLN
gt; 6.1.2005 | JOHN | LINCOLN
gt; 8.1.2005 | JOHN | LINCOLN
gt; 10.1.2005 | DAVID | LINCOLN
gt; 11.1.2005 | JIM | LINCOLN
gt; 10.1.2005 | DAVID | BMW
gt; 10.1.2005 | DAVID | MERCEDES
gt;
gt; How do i get the results regarding DAVID?
gt; Like this:
gt;
gt; 10.1.2005 | DAVID | LINCOLN | BMW | MERCEDES
gt;
gt;
gt; When i use Vlookup i get only the first result,or if i put true at the
gt; end of the Vlookup gormula it gives a wrong result.
gt; Please help me!
gt;
gt;
gt; --
gt; skarbanan
gt; ------------------------------------------------------------------------
gt; skarbanan's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29901
gt; View this thread: www.excelforum.com/showthread...hreadid=496084
gt;

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: www.excelforum.com/member.php...oamp;userid=29901
View this thread: www.excelforum.com/showthread...hreadid=496084So you are cheating?

--
Regards,

Peo Sjoblom

(No private emails please)quot;skarbananquot; gt; wrote in
message ...
gt;
gt; Well i'km doing this for a school assigment and the prof. said that we
gt; MUST use a formula and NO filter or pivot table.
gt; Now i will try the formula's submitted
gt;
gt; Thanks!
gt;
gt;
gt; --
gt; skarbanan
gt; ------------------------------------------------------------------------
gt; skarbanan's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29901
gt; View this thread: www.excelforum.com/showthread...hreadid=496084
gt;
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: www.excelforum.com/member.php...oamp;userid=29901
View this thread: www.excelforum.com/showthread...hreadid=496084Does 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)quot;skarbananquot; gt; wrote
in message ...
gt;
gt; No i'm not cheating.It's not a shasme to ask for help, it's a shame to
gt; refuse to give help. I would like to see your face after weeks of
gt; working on assigments and not be able to do something more.
gt; I challenge you....why don't you try to solve it? hehe
gt;
gt;
gt; --
gt; skarbanan
gt; ------------------------------------------------------------------------
gt; skarbanan's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29901
gt; View this thread: www.excelforum.com/showthread...hreadid=496084
gt;