Urgent help needed!

**skarbanan** · 12-26-2005, 07:33 PM

Ron Coderre · 12-26-2005, 08:15 PM

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
>
>

Bob Phillips · 12-26-2005, 08:20 PM

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
>

Dave Peterson · 12-26-2005, 08:35 PM

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

CLR · 12-26-2005, 10:15 PM

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
>

Biff · 12-26-2005, 11:25 PM

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
>

**skarbanan** · 12-27-2005, 07:07 AM

Well i'km doing this for a school assigment and the prof. said that we MUST use a formula and NO filter, pivot table or VB.

Now i will try the formula's submitted

I tried to implement the forumulas but i got nothing out
I'm sending you my document and i hope that you will helo my out...

Peo Sjoblom · 12-27-2005, 01:10 PM

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
>

**skarbanan** · 12-27-2005, 01:17 PM

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

Peo Sjoblom · 12-27-2005, 02:15 PM

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
>