table question-defined

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Me - 21 Apr 2004 17:09 GMT

Good day, I've asked this before but not well enough. I have a table that
looks like below:

date deal deal# rep amt
status

01/01/01 GK 1 tom 349 app
01/01/01 GK 2 nancy 698 app
01/01/01 GK 3 harry 698 app
01/01/01 GK 4 tom 174 app
01/01/01 GK 5 nancy 698 app

What I would like is to be able to seperate each of the entries (sorted by
"rep") onto a designated area on a separate worksheet like so:

date deal deal# rep amt
status
01/01/01 GK 1 tom 349 app
01/01/01 GK 4 tom 174 app

date deal deal# rep amt
status
01/01/01 GK 2 nancy 698 app
01/01/01 GK 5 nancy 698 app

date deal deal# rep amt
status
01/01/01 GK 3 harry 698 app

I'm not sure if I should be using INDEX or VLOOKUP or what. Any help would
be greatly appreciated.

-Rob

Reply to this Message

Don Guillett - 21 Apr 2004 17:53 GMT

Just use window>freeze pane at row 3 and this macro

Sub separteem()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
If Cells(i, 4) <> Cells(i - 1, 4) Then
'MsgBox Cells(i, 4)
Cells(i, 4).Resize(1, 4).EntireRow.Insert
End If
Next
End Sub

Signature

Don Guillett
SalesAid Software
donaldb@281.com

> Good day, I've asked this before but not well enough. I have a table that
> looks like below:
[quoted text clipped - 29 lines]
>
> -Rob

Reply to this Message

Don Guillett - 21 Apr 2004 18:01 GMT

forgot to sort by Rep first

Sub separteem()
x = Cells(Rows.Count, 1).End(xlUp).Row
Range("A4:F" & x).Sort Key1:=Range("d4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For i = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
If Cells(i, 4) <> Cells(i - 1, 4) Then
Cells(i, 4).Resize(1, 4).EntireRow.Insert
End If
Next
End Sub

Signature

Don Guillett
SalesAid Software
donaldb@281.com

> Just use window>freeze pane at row 3 and this macro
>
[quoted text clipped - 49 lines]
> >
> > -Rob

Reply to this Message

Me - 21 Apr 2004 18:41 GMT

Thanks for looking Don. Unfortunately I am bit apprehensive about using code
to accomplish my goals. I know very little about it and the people that will
use this after I am gone know nothing about Excel so I am hoping to
accomplish this using only formulas so I can be of help to them if they need
it.

Is there such a way that you know of? I don't mind filling in all the cells
with formulas, even if it means a giant file.

As plain as I can put it, I'd like to do something like...

if(deals!g2:g2401="this particular rep", transfer that entire row to the
dedicated cells on the other sheet,"")

I hope I am making sense here.

-Rob

> forgot to sort by Rep first
>
[quoted text clipped - 67 lines]
> > >
> > > -Rob

Reply to this Message