table question-defined

  • Thread starter Thread starter Me
  • Start date Start date
M

Me

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