Move values in rows at the end of the preceding row *SOLVED*

[Norman]

Dear all,

I have an excelsheet where the dataset is store in column A in in a CSV format.
I am looking for a code starting from A1 to remove the rows with text string and to move some numbers stored in rows <>"2018" at the end of the previous row. I have posted a question in another thread to adress the text removing part but I am looking for the how to move the numbers <>2018 part.

s an example of the dataset:

Configurable Alerts
Report ID:
Run Date:
Run Time:
TEXT_SYS801
16/12/2018
22:30:49 UTC
Page 1 of 34
Enterprise Blablabla
78) Blablabla data blablabla by blabla (medium priority)
This alert gives bla bla bla bla to partners.
2018,...,...
2018, 1, 95716, 10021703, NA, TEXT (TEXT), PHL, TEXT,Text Text Text,
10031793, 2448.83
2018,...,...
3000.95
2018,...,...
2018,...,...

Configurable Alerts
Report ID:
Run Date:
Run Time:
TEXT_SYS801
16/12/2018
22:30:49 UTC
Page 2 of 34
2018,...,...
2018,...,...
2018,...,...

Any idea?

I have a put a question in the following thread to adjust the first part of the code but have not heard anything yet
http://www.excelfox.com/forum/showth...-on-Conditions

Many thanks for the help and sharing knowledge

Cheers
Norman

[DocAElstein]

Hello again Norman
As I mentioned , I am not too familiar with the code from Admin ( http://www.excelfox.com/forum/showth...tions#post3640 ) , but it sounds like something like your requirement can be done in many ways using VBA.
( Admin is using some sort of advanced filtering technique which I don’t have a lot of experience with.
I would probably use a simple coding technique which might be marginally slower in running, but I doubt that anyone would notice or be worried about the minute increase in time. If you are not too experienced with VBA, then a simpler code might be easier for you to understand and maintain or modify later. )

For me it would be easier if I could see a reduced data example showing me what you have “Before” any code runs, and then show me, perhaps on another worksheet an “After” which shows what you want after the code has run.

The After should be based on the data in the Before. Keep the data to a minimum, but pick it carefully so that it shows all possible scenarios. ( Don’t post any sensitive data, - simply change real data or make the data up, but keep it in a typical form so that it can be used to test the code in all possible scenarios).

At excelfox you can upload a file. If you have not done that before at a forum like this, then it may not be so obvious how to do that. Here are some notes that might help: http://www.excelfox.com/forum/showth...age9#post10769
If you are not familiar with posting at a forum like excelfox , then it might be useful for you to start a practice thread here: http://www.excelfox.com/forum/forumd...p/17-Test-Area . Give the Thread a title anything like “Just practicing posting”. Then you can practice things like uploading a file, using code tags , posting tables .. etc…

I am off for a short time now. I will check back again later
Alan

[Norman]

Hello DocAElstein

Many thanks for the guidance and the info

I have uploaded the file so that you can have a better idea of what is the intended output (After based on Before input)
The cleansing should:
- Delete rows with text strings, dates and blank rows
- Moving values <> 2018 to the preceding row at the end.
I am not sure though if it is easier to use the text to column feature before hand to move the number values at the end of the previous row.

I hope it makes sense and thank you for the help much appreciated

Cheers
Norman

[DocAElstein]

Hi Norman,

...I have uploaded the file so that you can have a better idea of what is the intended output (After based on Before input)

I don't see any file ??

No Norman File.JPG : https://imgur.com/PtIxkfN
No Norman File.JPG

Alan

[Norman]

In the first post you will find the attachment

Hi Norman,

I don't see any file ??

Cheers
Norman

[DocAElstein]

OK , sorry , I missed that.
I will take a look at your file when I have time
Alan

[DocAElstein]

Hello Norman,
This looks fairly easy, but I may have missed something…

But anyway, here we go…
Looking at your sample data, it appears that all we need to do is
_1) to look for a value, or rather a text, of “2018,” at the start each cell .Value
_2) If we do not have the “2018,” then we check to see if we have those “loose numbers”

The simplest thing to do then would be to collect rows of data having the “2018” at the start. One way to do this would be to loop through the rows of data and put the data values having the “2018” at the start into an array that has the size of the complete original data range.
At each loop we will also check to see if we had those “loose numbers” and if so, tack them on to the last output array value. Note: For the code to work, I have assumed that if we have a row of “loose numbers” , then at least the first 4 characters will be part of a number. I also assume that the rows that you do not want will never have something looking like a number as the first 4 digits.


Once we finished that we will have an array that has the wanted rows at the start and the rest of the “rows” in that array will be empty.
So if we then paste that array of values back into the worksheet, then we will get you wanted output

I will give you the code below to do that, and if you then want anything different or want me to explain anything more about what is going on , then let me know.. , I will take another look in, ( maybe tomorrow).
I have put the output in a new worksheet, but to put it in the original worksheet, then I think you can see where to change Tabelle1 to BEFORE or whatever…

Alan
_.______________________________________________

BEFORE
_____ Workbook: Data Sample.xlsm ( Using Excel 2007 32 bit )

Row\Col	A
1	Configurable Alerts blabla:
2	Run Date:
3	Run Time:
4	HCR_SYS801
5	16/12/2018
6	22:30:49 UTC
7	Page 1 of 34
8	Enterprise Blabla Mgt
9	78) Blabla allocation by position (medium blabla)
10	This alert gives the monthly blabla of blabla in BLABLA to enable the blabla to fund raising partners.
11	2018, 1, 90515, 10024515, G9, SBlabla (HQ), CHE, BLABLA, blabla, 10012098, 12003.5
12	2018, 1, 90629, 10022334, P3, BLABLA blabla (blablabla), CHE, BLABLA,blabla, 10033609, 13941.72
13	2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah,
14	10006098, 15392.64
15	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla,
16	10005678, 16231.7
17	2018, 1, 90712, 10022908, P4, Snr BLA Off (Strat BLa, BLA), CHE, BLABLA,Bla BLabla, 10023234,
18	14900.28
19	2018, 1, 90919, 10020984, P2, Ass BLA Balbla, CHE, BLA,Blabla, 10033098, 10486.33
20	2018, 1, 95706, 10023098, NB, Assc BLA Blabal (LatBLAa), BLA, BLABLABLA,Blabla, 10034318,
21	7566.31
22	Configurable Alerts Report ID:
23	Run Date:
24	Run Time:
25	HCR_SYS801
26	16/12/2018
27	22:30:49 UTC
28	Page 2 of 34
29	2018, 1, 95716, 10018763, NA, Asst BLA Off (Blabla & Multi-BLa), BLA, BLA,Bla, 10097776, 8607.96
30	2018, 1, 99716, 10026132, G5, Snr BLA Asst (Bla Blabla), BLA, BLABLA,bla BLa, 18767043, 5477.44
31	2018, 1, 99716, 10016545, G6, Blabla Blabla (BLA), BLA, BLABLABLA,Blabla, 1097029,
32	5325.3
33
34	Configurable Alerts Bla bla:
35	Run Date:
36	Run Time:
37	HCR_SYS801
38	16/12/2018
39	22:30:49 UTC
40	Page 3 of 34
41

Worksheet: BEFORE

After running the code:
_____ Workbook: Data Sample.xlsm ( Using Excel 2007 32 bit )

Row\Col	A
1	2018, 1, 90515, 10024515, G9, SBlabla (HQ), CHE, BLABLA, blabla, 10012098, 12003.5
2	2018, 1, 90629, 10022334, P3, BLABLA blabla (blablabla), CHE, BLABLA,blabla, 10033609, 13941.72
3	2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah,10006098, 15392.64
4	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla,10005678, 16231.7
5	2018, 1, 90712, 10022908, P4, Snr BLA Off (Strat BLa, BLA), CHE, BLABLA,Bla BLabla, 10023234,14900.28
6	2018, 1, 90919, 10020984, P2, Ass BLA Balbla, CHE, BLA,Blabla, 10033098, 10486.33
7	2018, 1, 95706, 10023098, NB, Assc BLA Blabal (LatBLAa), BLA, BLABLABLA,Blabla, 10034318,7566.31
8	2018, 1, 95716, 10018763, NA, Asst BLA Off (Blabla & Multi-BLa), BLA, BLA,Bla, 10097776, 8607.96
9	2018, 1, 99716, 10026132, G5, Snr BLA Asst (Bla Blabla), BLA, BLABLA,bla BLa, 18767043, 5477.44
10	2018, 1, 99716, 10016545, G6, Blabla Blabla (BLA), BLA, BLABLABLA,Blabla, 1097029,5325.3
11
12
13
14
15

Worksheet: Tabelle1

Normans data arrays.JPG : https://imgur.com/FchmkiG
Normans data arrays.JPG

Code:

Sub Just2018lines()
Dim Lr As Long ' variable for last row number with data in it 
 Let Lr = Worksheets("BEFORE").Range("A" & Rows.Count & "").End(xlUp).Row ' Lr : http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10192#post10192 : https://pastebin.com/1adPpV6J : https://www.excelforum.com/hello-introduce-yourself/1214555-an-old-geezer-coming-over-from-the-access-forum.html
Dim arrIn() As Variant ' We will put our range of data into this. I need Variant, simply because .Value applied to a range returns the data field in variant types
 Let arrIn() = Worksheets("BEFORE").Range("A1:A" & Lr & "").Value
Dim arrOut() As String ' I can define the type as I have a fixed size array that I will be filling in a loop. ( I have to use ReDim Statement below, as Dim only takes numbers)
 ReDim arrOut(1 To UBound(arrIn(), 1), 1 To 1) ' Out output array has the same size as the input array
Dim Cnt As Long, RwOut As Long ' Count of all data rows, Count for "rows" in oputput array
    For Cnt = 1 To Lr Step 1
        If Left(arrIn(Cnt, 1), 5) = "2018," Then ' _1) look for a value, or rather a text, of "2018," at the start each cell .Value
         Let RwOut = RwOut + 1 ' next "row" in output array
         Let arrOut(RwOut, 1) = arrIn(Cnt, 1) '
        Else '
            If IsNumeric(Left(arrIn(Cnt, 1), 4)) Then ' _2) check to see if we have those "loose numbers"
             Let arrOut(RwOut, 1) = arrOut(RwOut, 1) & arrIn(Cnt, 1) ' The last row filled in the output array has the "loose numbers" tacked on
            Else
            End If
        End If
    Next Cnt
 Let Worksheets("Tabelle1").Range("A1:A" & Lr & "").Value = arrOut() 'VBA allows us to paste to the values in range in one go from an array , via the .Value property
 Worksheets("Tabelle1").Columns("A:A").AutoFit
End Sub

[Norman]

Dear Alan,

Absolutely amazing!! It works like a charm ...
For someone who is not familiar with VBA, I am really impressed by the code and more importantly by the explanation to understand what is going on behind the scene...really appreciated!!

Many thanks indeed and wish you all the best,

Cheers
Norman

[DocAElstein]

You’re welcome
Thanks for the feedback
Alan

[xladept]

Hello Norman and Doc,

A Filter with wildcard also works:

Sub NormanXLFox(): Dim wa As Worksheet, wb As Worksheet
Set wb = Sheets("Before"): Set wa = Sheets("After")
        wb.UsedRange.AutoFilter 1, "=2018*"
wb.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Copy _
            wa.Cells(1, 1)
wb.UsedRange.AutoFilter: End Sub