Need Help Sorting a List

[RJD1993]

I have information in column A and need certain information to be moved into columns B & C.



Game Review User
1






97






Grand Theft Auto V





User: 8.2





18-Nov-14





2






95






The Last of Us Remastered





User: 8.9





29-Jul-14





3






93






Metal Gear Solid V: The Phantom Pain





User: 8.2





1-Sep-15





4






92






Journey





User: 8.2





21-Jul-15





5






92






Bloodborne





User: 8.6





24-Mar-15





6






92







I don't really know where to begin with this which is why I don't have any code for you guys to check out which doesn't give you much to work with, but basically what I'm trying to do is delete the first number, move the second number into the "review" column (B), leave the game name, move the user score into the "user" column (C), and delete the date. Then, if possible, remove the blank rows in between the games. I can use the record function for the last part, but I tried for the rest of it and tried to replicate it for everything, but it's messy and there's too much going on.

[SamT]

I think I understand, but just to be clear, do me a favor.

Put that list in Column B for now, and in Column A put the titles you have for each item, "game name," "user score," "date," Etc. Then in Columns C, D, E, F, G as needed, Row 1, repeat those titles as used, and underneath them, show us exactly how you want it to be. Don't worry about deleting empty rows, that is a common task.

Now, In Excel, copy all that and simply paste it into the message here.

[RJD1993]

Game
List #
Review
User
Date


List #
1






Review
97






Game
Grand Theft Auto V






User
User: 8.2






Date
18-Nov-14






List #
2






Review
95






Game
The Last of Us Remastered






User
User: 8.9






Date
29-Jul-14






List #
3






Review
93






Game
Metal Gear Solid V: The Phantom Pain






User
User: 8.2






Date
1-Sep-15






List #
4






Review
92






Game
Journey






User
User: 8.2






Date
21-Jul-15






List #
5







Game
Review
User





Grand Theft Auto
97
8.2





The Last of Us
95
8.9





Metal Gear Solid 5
93
8.2





Journey
92
8.2












I think this is what you mean. Basically I want to delete the the list # and the date and close the space between the game names, while also sorting the review scores into one column and the user scores into another. If you could also find a way to delete the "User:" in front of the actual score that would be cool, but only if it's easy, otherwise I'll just do it myself. If it's easier to just sort the list # and date, that's easy to delete afterwards, so no big deal. I just appreciate any help, I'm having a hard time with this.

[SamT]

I set up sheet 1 like this. The top left cell, Game, in this table is "A1" in your sheet. The code starts in Row 2


Game

Review
User



1




97




Grand Theft Auto V




User: 8.2




18-Nov-14




2




95




The Last of Us Remastered




User: 8.9




29-Jul-14




3




93




Metal Gear Solid V: The Phantom Pain




User: 8.2




1-Sep-15




4




92




Journey




User: 8.2




21-Jul-15





Then I put this code in Sheet1's code page

Option Explicit

Sub ReArrangeReviewList()

Dim LR As Long 'Last Row number
Dim rw As Long 'Generic Row number
Dim ReviewList As Range
Dim TF As Boolean 'TrueFalse
Dim WsF As Object
Set WsF = Application.WorksheetFunction

With Sheets("Sheet1")

'Remove "User:" from User Cells
LR = Cells(Rows.Count, "A").End(xlUp).Row
Set ReviewList = Range("A2:A" & LR)
TF = ReviewList.Replace("User: ", "")
Set ReviewList = Nothing

'Delete Date Rows
For rw = LR To 2 Step -5
.Rows(rw).Delete
Next rw

'Delete List# Rows
LR = Cells(Rows.Count, "A").End(xlUp).Row - 3
For rw = LR To 2 Step -4
.Rows(rw).Delete
Next rw

'Move Review and User Values
LR = Cells(Rows.Count, "A").End(xlUp).Row
For rw = 2 To LR Step 3
Cells(rw, "A").Cut (Cells(rw, "B"))
Cells(rw + 1, "A").Cut (Cells(rw, "A"))
Cells(rw + 2, "A").Cut (Cells(rw, "C"))
Next rw

'Delete Empty Rows
LR = Cells(Rows.Count, "A").End(xlUp).Row
For rw = LR To 2 Step -1
If WsF.CountA(.Rows(rw)) = 0 Then .Rows(rw).Delete
Next rw
End With
End Sub