How split column into multiple columns for multiples sheets
Solved/Closed
Hasson_6770
Posts
18
Registration date
Monday August 2, 2021
Status
Member
Last seen
May 23, 2022
-
Updated on Feb 7, 2022 at 04:57 AM
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022 - Feb 15, 2022 at 08:20 AM
Hasson_6770 Posts 18 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022 - Feb 15, 2022 at 08:20 AM
Related:
- Split excel sheet into multiple sheets based on column value
- Mark sheet in excel - Guide
- Google sheet right to left - Guide
- How to open excel sheet in notepad++ - Guide
- How to screenshot excel sheet - Guide
- Little alchemy cheat sheet - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Updated on Feb 10, 2022 at 11:31 AM
Updated on Feb 10, 2022 at 11:31 AM
Hi Hasson,
Ok, it's just that some people don't realise what formula's can do.
Here is the requested code:
PS: stop drugging your formula's, it isn't good for them :)!
Best regards,
Trowa
Ok, it's just that some people don't realise what formula's can do.
Here is the requested code:
Sub RunMe() Dim lRow As Long Dim mFind As Range Sheets("RP").Select lRow = Range("B" & Rows.Count).End(xlUp).Row For Each cell In Range("B2:B" & lRow) With Sheets("REP1") Set mFind = .Columns("B").Find(cell.Value) If Not mFind Is Nothing Then .Range("C" & mFind.Row).Value = cell.Offset(0, 1).Value .Range("D" & mFind.Row).Value = cell.Offset(0, 2).Value .Range("E" & mFind.Row).Value = cell.Offset(0, 3).Value End If End With With Sheets("PROC1") Set mFind = .Columns("B").Find(cell.Value) If Not mFind Is Nothing Then .Range("C" & mFind.Row).Value = cell.Offset(0, 1).Value .Range("D" & mFind.Row).Value = cell.Offset(0, 2).Value .Range("E" & mFind.Row).Value = cell.Offset(0, 3).Value End If End With Next cell End Sub
PS: stop drugging your formula's, it isn't good for them :)!
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Feb 8, 2022 at 11:46 AM
Feb 8, 2022 at 11:46 AM
Hi Hasson,
It seems to me like you could just use VLOOKUP functions for that.
Sheet REP1, cell C2: =VLOOKUP($B2,RP!$B$2:$E$1000,2)
Sheet REP1, cell D2: =VLOOKUP($B2,RP!$B$2:$E$1000,3)
Sheet REP1, cell E2: =VLOOKUP($B2,RP!$B$2:$E$1000,4)
Then drag those formula's down.
Do the same for Sheet PROC1, or select both sheets while holding CTRL to only have to do it once.
Best regards,
Trowa
It seems to me like you could just use VLOOKUP functions for that.
Sheet REP1, cell C2: =VLOOKUP($B2,RP!$B$2:$E$1000,2)
Sheet REP1, cell D2: =VLOOKUP($B2,RP!$B$2:$E$1000,3)
Sheet REP1, cell E2: =VLOOKUP($B2,RP!$B$2:$E$1000,4)
Then drag those formula's down.
Do the same for Sheet PROC1, or select both sheets while holding CTRL to only have to do it once.
Best regards,
Trowa
Hasson_6770
Posts
18
Registration date
Monday August 2, 2021
Status
Member
Last seen
May 23, 2022
Feb 8, 2022 at 02:16 PM
Feb 8, 2022 at 02:16 PM
Hi Trowa,
I appreciate to try helping me by formula
as I said I have about 1000 rows . So I search for macro to big data instead of every time drug the formula when increase data. Actually I search too much in Google but I don't find like my request.
Thanks
I appreciate to try helping me by formula
as I said I have about 1000 rows . So I search for macro to big data instead of every time drug the formula when increase data. Actually I search too much in Google but I don't find like my request.
Thanks
Updated on Feb 11, 2022 at 02:54 PM
You're legend! this is exactly what I need it.
Just I want fixing the headers into two sheets when split should replace the headers as the same thing in sheet RP . I'm taking about columns D,E into sheets Rep1,Proc1.
best regards,
Hasson
Feb 14, 2022 at 12:00 PM
To fix the headers, add the following code lines between 25 (Next cell) and 26 (End sub):
Best regards,
Trowa
Feb 15, 2022 at 08:20 AM
many thanks for your assistance.