Hi,
I have attached the xls file I'm working on to make it easier for you to understand.
First thing is I want to filter data to only blank cells on C ("2nd Name"). Simple enough.
Then, the first level sort is I want to sort ascending on D ("Preference), 2nd level sort ascending on E ("Novice/Pro") Simple enough.
Here's the difficult part.
I want to check if the first row is a novice or pro, if novice, go down the row and find a pro. Copy the name of the pro and add to the 2nd name column (at C) and then delete the entire row of the pro. If no pro can be found, search for a novice and pair up. If no novice also, stop.
If pro, go down the row and find a novice. Copy the name of the novice and paste to the 2nd name column (at C) and then delete the entire row of the novice. If no novice can be found, seach for a pro and pair up. If no pro also, stop.
In pseudo-code, here is my idea:
ActiveSheet.Filter(Column C Blank Cells)
ActiveSheet.Sort (1st level: Ascending D, 2nd level: Ascending E)
Row.Select(First Visible Row)
ForEachRow that is Visible
{
if (ColumnE.Value =/= SelectedRowColumnE.Value && NotBlank)
{
Copy ColumnB.Value
Paste at SelectedRowColumnC
Delete Row
Row.Select (SelectedRow + 1)
}
}
ActiveSheet.Filter(Column C Blank Cells)
Row.Select(First Visible Row)
ForEachRow that is Visible (Starting at 2nd Row)
{
if (ColumnE.Value is NotBlank)
{
Copy ColumnB.Value
Paste at SelectedRowColumnC
Delete Row
Row.Select (SelectedRow + 1)
}
}
1. I'm not sure if my pseudo-code even logically works
2. I'm not sure how to translate this to VBA code because I have to handle visible cell types, create foreach loops that only has visible types etc. I don't have any experience in this!
Any help is very much appreciated, thank you very much! :D
Bookmarks