Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 26th 05, 11:33 PM 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  
Old December 27th 05, 12:10 AM 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  
Old December 27th 05, 12:14 AM 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  
Old December 27th 05, 12:28 AM 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  
Old December 27th 05, 02:13 AM 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  
Old December 27th 05, 03:19 AM 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  
Old December 27th 05, 11:07 AM 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  
Old December 27th 05, 05:05 PM 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  
Old December 27th 05, 05:17 PM 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  
Old December 27th 05, 06:13 PM 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




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 10:32 PM
Urgent help needed: IF function Terence Excel Worksheet Functions 3 November 16th 04 02:29 AM


All times are GMT +1. The time now is 05:03 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017