Urgent help needed!

  • Thread starter Thread starter skarbanan
  • Start date Start date
S

skarbanan

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 th
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
 
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)
 
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
 
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
 
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
 
Well i'km doing this for a school assigment and the prof. said that w
MUST use a formula and NO filter or pivot table.
Now i will try the formula's submitted

Thanks
 
No i'm not cheating.It's not a shasme to ask for help, it's a shame t
refuse to give help. I would like to see your face after weeks o
working on assigments and not be able to do something more.
I challenge you....why don't you try to solve it? heh
 
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 said:
Listen, i'm here to talk about excel, if you are not please do your
posts elsewhere.
Click to expand...

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 b
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 n
pivot tabel no nothing but only excel database formula...so...i'll b
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.
Click to expand...

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
 
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
 
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!
 
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
 
Back
Top