Any Excel experts out there?

[SnDvls]

here's what I have


Sheet #1
Column A = different names
Column B = Yes or No

example

Joe Blow Yes
Jack Imhoff Yes
Bob Doe No
Barry White Yes
James Jones No
Brian Wilson No


I want to be able to in sheet #2 read and input down the list sot that it comes out like this in two columns

Joe Blow Bob Doe
Jack Imhoff James Jones
Barry White Brian Wilson


basically see if column B in sheet 1 = oppoiste on the next name insert it if it's the same put it down one...ect.

any ideas?

[KevinNU7]

Why do you need the info like that?

Why can't you just sort by column two and then cut all the Nos into column 2

[SnDvls]

I want to input it in one sheet and then have it output on another.

[Ramzavail]

I'm good at excel, but I'm confused about what you want.

[SnDvls]

I want it to read sheet 1's info and put them in sheet 2 but opposites

example

Joe Blow Yes
Jack Imhoff Yes
Bob Doe No
Barry White Yes
James Jones No
Brian Wilson No

so it reads Joe Blow and yes and puts him in then reads the next name and sees that it is also a yes so it puts hime below Joe Blow, then it reads name #3 and sees it equals a no so it puts it next to Joe Blow ect.

too confusing?

it's kinda like a match maker I guess for boxing or wrestling is what I'm looking for.

[QuikSand]

Sorting/pasting seems to be the easiest way to do this, though I too am a bit baffled about your desired outcome. I'd sort the list by the second column, and then manually cut and paste the "No" group's names to be in column two.

There may be a higher tech solution, but if you're just doing this once, that ought to work fine.

*shurg*

[SnDvls]

well they are rankings so I don't want to sort it and disrupt the order as I want the two highest ranked people to face off.

[SnDvls]

dola -
I don't know if it is even possible in excell

[QuikSand]

So add a numbered colum to go along with the original rankings... and have that be your second sort after the Yes/No column. Then paste the No group next to the Yes group.

[SnDvls]

Originally Posted by QuikSand

So add a numbered colum to go along with the original rankings... and have that be your second sort after the Yes/No column. Then paste the No group next to the Yes group.

even if it's not a one time thing?

I'd like to just type in the rankings and then have it make the fights based off the rankings.

[QuikSand]

Best of luck to you.

[KevinNU7]

I could probably think of something with vlookups but I am getting on a plane now. Maybe tomorrow if boredom sets

[Pumpy Tudors]

I think the only way to do this is to run a macro on it. That means you'd have to write the macro, of course, and I don't know the language well enough to help with that. It has to be a macro because you're going to compare a condition ("yes" or "no") and that will determine which cell you're going to fill on sheet #2. Since the destination cell isn't determined until after you've checked the condition, there's really no other way to do this.

[SnDvls]

well if anyone thinks of anything I'd appriciate it...thanks all again.

[VPI97]

Originally Posted by Pumpy Tudors

I think the only way to do this is to run a macro on it. That means you'd have to write the macro, of course, and I don't know the language well enough to help with that. It has to be a macro because you're going to compare a condition ("yes" or "no") and that will determine which cell you're going to fill on sheet #2. Since the destination cell isn't determined until after you've checked the condition, there's really no other way to do this.

Yeah..here you go:

Sub Sorting_Macro()
    iRow = 1
    iCurYesRow = 1
    iCurNoRow = 1
    iCellLen = 1
    While iCellLen > 0
        iCellLen = Len(Worksheets(1).Cells(iRow, 1).Value)
        If iCellLen > 0 Then
            szBool = Trim(Worksheets(1).Cells(iRow, 2).Value)
            Select Case szBool
                Case "Yes"
                    Worksheets(2).Cells(iCurYesRow, 1) = Worksheets(1).Cells(iRow, 1).Value
                    iCurYesRow = iCurYesRow + 1
                Case "No"
                    Worksheets(2).Cells(iCurNoRow, 2) = Worksheets(1).Cells(iRow, 1).Value
                    iCurNoRow = iCurNoRow + 1
            End Select
        End If
        iRow = iRow + 1
    Wend
End Sub

Just save that macro to your Worksheet and run it when you need sorting.

[jbmagic]

Here a very helpful excel forum.

http://www.mrexcel.com/board2/

[Subby]

VPI is such a pimp.