Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
OldFella

Selectively Writing Horizontally Occurring Values to a Column

Question

Hi Caleeco

Here's the new issue that I referred to last night. I have also emailed to you a sample worksheet so that the following notes make some sense. The sample worksheet, with some fairly old data, illustrates my problem.

Column O interogates the data in columns D - G and returns a value when conditions are true. That value then triggers columns Q - V. There is one time interval between each of the columns Q - V; on this sheet the time interval is 12 hr (col C). I'm only interested in cells in col R - V that return either RV or FO; (you'll note from the underlying formula that col Q will only ever return LC or Not LC). The occurrences of RV and FO then have to be copied to their corresponding cell in col H - for which I'd appreciate some masterful VBA.

I've manually transferred the values to col H to illustrate the desired end output. In essence it's a case of reading horizontally across col R - V and writing the RV and FO values, if any, vertically down col H.

You can probably see that the cell in col Q of any given row corresponds to the cell in col H of that row. I've colour coded one set of cells to make the equivalence more obvious.

A potential source of error is that if the "SH" occurrences are close enough together, more than one cell from col R - V will try to copy to col H. You can see from the sheet with the arrows on it that there are three examples of this in the lower area. The rules here are that:
a)  An RV adding to an RV copied from a preceding row remains as an RV;
b)  An RV adding to an FO copied from a preceding row remains as an FO;
c)  An FO adding to an RV copied from a preceding row replaces the RV; and
d)  An FO adding to an FO copied from a preceding row remains as an FO.

Hope that is all clear; really looking forward to the outcome as this little issue is currently a major stumbling block with advancing the further development of the worksheet.

And hey!  Did you notice?  There's not one cell on either worksheet that displays as a blank but has something hidden, lurking beneath the surface.  Progress!!  And while I'm on this topic, the lesson learned with the previous problem is:  Only copy VBA code from the forum; do NOT copy it from the emailed notification (because it puts line breaks in that shouldn't be there!). Any other forum users reading this - take note!

 

  • Like 1

Share this post


Link to post
Share on other sites

13 answers to this question

Recommended Posts

  • 0

All done mate!

 

The new blocks raised an interesting problem! The previous blocks (LC, RV, FO FO etc) were all contiguous, where as the new blocks sometimes had blanks in the first column eg SL+1. This caused some problem with the existing code. So I have modified it to search how wide the header rows are (row 1) and use that as the search width. 

 

This means a NEW version of the code. I have also incorporated another argument to the function for your latest requirement eg. when you're looking for FO values only! Sorry, for giving you another round of testing to do. Let me know how you get on:

Sub FO_RV_SH()
    'Call DataProcesser(Sheet Name, the code you're looking for eg SL/SH, The column the SL/SH is in, the column you want to output to, is it FO/RV or just FO data?)
    Call DataProcesser("Back-test Layout v3", "SH", "Q", "H", "FO/RV")
End Sub

Sub FO_RV_SL()
   Call DataProcesser("Back-test Layout v3", "SL", "Y", "L", "FO/RV")
End Sub

Sub FO_SH()
    Call DataProcesser("Back-test Layout v3", "SH", "AG", "I", "FO")
End Sub

Sub FO_SL()
    Call DataProcesser("Back-test Layout v3", "SL", "BM", "M", "FO")
End Sub

Function DataProcesser(wsBT As String, sYesNo As String, sYesNoCol As String, sOutputCol As String, sType As String)
'//Source: www.ExcelWTF.com
'//Purpose: to process horizontal data entries and transpose them with set criteria

    Dim ws As Worksheet
    Dim rYesNO As Range, r As Range
    Dim lr As Long, items As Long, i As Long
    Dim sItem As String
    
    Application.ScreenUpdating = False
    Set ws = Sheets(wsBT)
    lr = ws.Range(sYesNoCol & Rows.Count).End(xlUp).Row
    Set rYesNO = ws.Range(sYesNoCol & "3:" & sYesNoCol & lr)
    items = ws.Range(Cells(1, ws.Range(sYesNoCol & 1).Column), Cells(1, ws.Range(sYesNoCol & 1).Column).End(xlToRight)).Count
    
    For Each r In rYesNO
        If r.Value = sYesNo Then
            For i = 0 To items - 1
                sSeq = ws.Range(sYesNoCol & r.Row).Offset(, i + 1).Value
                If sType = "FO" Then
                    If sSeq = "FO" Then ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO"
                Else
                    If sSeq = "RV" And sOutput = "RV" Then
                        ws.Range(sOutputCol & r.Row).Offset(i).Value = "RV"
                    ElseIf sSeq = "RV" And sOutput = "FO" Then
                        ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO"
                    ElseIf sSeq = "FO" And sOutput = "RV" Then
                        ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO"
                    ElseIf sSeq = "FO" And sOutput = "FO" Then
                        ws.Range(sOutputCol & r.Row).Offset(i).Value = "FO"
                    ElseIf sOutput = "" And sSeq = "FO" Or sSeq = "RV" Then
                        ws.Range(sOutputCol & r.Row).Offset(i).Value = sSeq
                    End If
                End If
            Next i
        End If
    Next r
    
    ws.Range(sOutputCol & ws.Range("G" & Rows.Count).End(xlUp).Offset(1).Row & ":" & sOutputCol & ws.Range(sOutputCol & Rows.Count).End(xlUp).Offset(1).Row).ClearContents
    Application.ScreenUpdating = False

End Function
Quote

If there's any coding advantage in having the blocks of SH formulae contiguous and the blocks of SL formulae contiguous?

 

In this case... no :P The code runs on individual blocks, so their placement in the sheet is irrelevant. however, THERE MUST BE AN EMPTY COLUMN BETWEEN THE BLOCKS in row 1

 

Hope that helps

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi Mate, 

 

Thanks for the question, looks like a challenge! :D 

I had to read the problem statement a few times lol, but I think I get what you're after. Thanks for the worked example (with arrows and colours), made it easier to understand :)

 

Let me get to coding! I will post back hopefully later this evening (9pm here currently).

 

Good Tip! (Copying from forum vs email) Thanks for sharing! 

 

Cheers

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hello, 

 

Coding done! I was unsure if this is the final result you wanted or a half step to the end goal? Will the columns O-V exist in your final sheet? or did you want the code to also calculate these?

 

The code below, assumes that columns O-V exist and have the SH, LLC, RV, FO etc markers to read. Please change the sheet name in line 5 if needed.  

 

Sub FO_RV()
'//Source: www.ExcelWTF.com
'//Purpose: Data processing, with modifier to add different string combinations

    Dim wsBackTest As Worksheet
    Dim rSH As Range, r As Range
    Dim items As Long, i As Long
    Dim sSeq As String, sColH As String
    
    Set wsBackTest = Sheets("Back-test Layout") ' Change this line to the correct sheet name
    Set rSH = wsBackTest.Range("O3:O" & wsBackTest.Range("O" & Rows.Count).End(xlUp).Row)
    
    For Each r In rSH
        If r.Value = "SH" Then
            items = Application.WorksheetFunction.CountIf(wsBackTest.Range("Q" & r.Row, "V" & r.Row), "?*")
            For i = 0 To items - 1
                sSeq = wsBackTest.Range("Q" & r.Row).Offset(, i).Value
                sColH = wsBackTest.Range("H" & r.Row).Offset(i).Value
                
                If sSeq = "RV" And sColH = "RV" Then
                    sColH = "RV"
                ElseIf sSeq = "RV" And sColH = "FO" Then
                    sColH = "FO"
                ElseIf sSeq = "FO" And sColH = "RV" Then
                    sColH = "RV"
                ElseIf sSeq = "FO" And sColH = "FO" Then
                    sColH = "FO"
                ElseIf sColH = "" And sSeq = "FO" Or sSeq = "RV" Then
                    sColH = sSeq
                End If
                
                wsBackTest.Range("H" & r.Row).Offset(i).Value = sColH
                
            Next i
        End If
    Next r

End Sub

This achieves the same output of your worked example. Hopefully it works on your end as expected, please do let me know. 

 

Many Thanks

Caleeco :D 

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco

First impressions - beautiful to watch! When I hit the FO-RV button and watch the symbols start appearing down col H, it's all I can do not to wet myself!

I'm going to give it a bit more extensive testing but based on what I just watched I don't anticipate any problems. If all is well then I'll get back to you, hopefully later today, with the next step.

Definitely a beer owing from this one!

 

  • Thanks 1

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco
Second impressions - exactly the same as the first impressions - brilliant!

One feature of my worksheets is that at this stage of their development they don't stand still. What you see today is unlikely to be what you see tomorrow. In line with this, the sample worksheet I sent you is already obsolete. Don't worry, all I've done is add in a few more columns. I modified your coding and changed the column letters and it all still functions as intended.

I've also added in a second block of formulae that performs a similar task to the first block. I'll email you a revised sheet so you can see what I've done. I then created a second block of your coding, adapted it to match these new columns, and it too runs as intended. The amended coding, and the new coding, are with the emailed worksheet - hopefully!

I'll leave it up to you whether you wish to extend the coding so that the helper columns are eliminated; from my perspective it's not important. This worksheet is actually just an intermediate step in the process; the main sheet accesses the values in col H - O using Index/Match based on date and time, so the appearance of this sheet is not critical. However - if you want the challenge of streamlining it, and especially if it improves the overall efficiency, then I'm more than happy to incorporate the upgrade.

I would appreciate one minor change though. When an SH or SL value occurs within the last few rows of data the coding continues to write FO/RV values below the last row of data. You'll see what I mean when you look at the emailed sheet. Can you please modify the coding so that it ceases writing FO or RV values below the last row of data.

I'm now going to spend a few days very carefully testing the coding. You'll see on the worksheet that there are two columns headed cT FO. These columns will display externally sourced FO values. I'm having some difficulty with the accuracy of the external input and I'm hoping that your coding will pick up all the instances of FO situations that the external source ought to determine - but doesn't. My gut feeling is that it won't pick them up and if that proves to be the case then I may come back to see if you can somehow develop code to pick them up. If you thrive on challenges, this will be one!!  :D   I'm currently endeavouring also to get correct results from the external data; if successful, that will negate the need for your challenge. 

Thanks once again for bringing it this far forward.

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

Thanks for the feedback! Looks like the logic is pretty much there. Didnt think of the problem of it extending beyond the last row! (I will add some code to fix this).

Spreadsheet received! Good to see you had a go at implementing your own version and building upon it further :D 

 

The helper columns, whilst not aesthetically pleasing, do offer efficiency savings. Coding it in VBA would:

  1. Increase macro running time
  2. Greatly increase the complexity of the code & make it harder to update.

So it would be probably not be worthwhile at this stage, especially if the sheet is subject to change. We can re-review your requirements when the final sheet is built. 

 

With regard to the 'ct FO' problem you mentioned, you could implement some sort of fuzzy lookup to find any entires that CONTAIN "FO" or "RV". Ok no problem, let me know how you get on. I love coding challenges so send it my way if the current method doesnt give you what you need. 

 

Right, requirement set, beer in hand... time to start coding!

Share this post


Link to post
Share on other sites
  • 0

All done mate, I think! Given your scope has expanded I took the liberty of making a more significant code edit. Instead of repeating the same macro twice for slightly different columns, criteria etc; it's the perfect opportunity to use a couple sub-routines with a FUNCTION. 

 

If you're not familiar with functions, it's essentially a template of instructions you want VBA to execute. Each individual Sub-routine will pass it the variables that are subject to change eg "SL" or "SH", Output to column H or L etc. This makes updating the code later easier, as you've not repeated two large chunks of code that are nearly identical. 

 

So delete the two existing subroutines you have, and replace it with this:

Sub FO_RV_SH()
    'Call FO_RVc(Sheet Name, the code you're looking for eg SL/SH, The column the SL/SH is in, the column you want to output to)
    Call FO_RVc("Back-test Layout v2", "SH", "Q", "H")
End Sub

Sub FO_RV_SL()
    'Call FO_RVc(Sheet Name, the code you're looking for eg SL/SH, The column the SL/SH is in, the column you want to output to)
    Call FO_RVc("Back-test Layout v2", "SL", "Y", "L")
End Sub


Function FO_RVc(wsBT As String, sYesNo As String, sYesNoCol As String, sOutputCol As String)

    Dim ws As Worksheet
    Dim rYesNO As Range, r As Range
    Dim lr As Long, items As Long, i As Long
    Dim sSeq As String, sColL As String
    
    Application.ScreenUpdating = False
    Set ws = Sheets(wsBT)
    lr = ws.Range(sYesNoCol & Rows.Count).End(xlUp).Row
    Set rYesNO = ws.Range(sYesNoCol & "3:" & sYesNoCol & lr)
    
    For Each r In rYesNO
        If r.Value = sYesNo Then
            items = Application.WorksheetFunction.CountIf(ws.Range(ws.Range(sYesNoCol & r.Row).Offset(, 1), ws.Range(sYesNoCol & r.Row).Offset(, 6)), "?*")
            For i = 0 To items - 1
                sSeq = ws.Range(sYesNoCol & r.Row).Offset(, i + 1).Value
                sOutput = ws.Range(sOutputCol & r.Row).Offset(i).Value
                
                If sSeq = "RV" And sOutput = "RV" Then
                    sOutput = "RV"
                ElseIf sSeq = "RV" And sOutput = "FO" Then
                    sOutput = "FO"
                ElseIf sSeq = "FO" And sOutput = "RV" Then
                    sOutput = "RV"
                ElseIf sSeq = "FO" And sOutput = "FO" Then
                    sOutput = "FO"
                ElseIf sOutput = "" And sSeq = "FO" Or sSeq = "RV" Then
                    sOutput = sSeq
                End If
                
                ws.Range(sOutputCol & r.Row).Offset(i).Value = sOutput
            Next i
        End If
    Next r
    
    ws.Range(sOutputCol & ws.Range("G" & Rows.Count).End(xlUp).Offset(1).Row & ":" & sOutputCol & ws.Range(sOutputCol & Rows.Count).End(xlUp).Offset(1).Row).ClearContents
    Application.ScreenUpdating = True
End Function

Let me know if you need more explanation for the setup above :) 

 

Alternatively, if you wish to keep two separate macros (as in the file you sent me). Add the line below to each respective sub-routine, just above the END SUB. This will stop the FO/RV being logged past the last row.

 

wsBackTest.Range("H" & wsBackTest.Range("G" & Rows.Count).End(xlUp).Offset(1).Row, "H" & wsBackTest.Range("H" & Rows.Count).End(xlUp).Row).ClearContents

and

wsBackTest.Range("L" & wsBackTest.Range("G" & Rows.Count).End(xlUp).Offset(1).Row, "L" & wsBackTest.Range("L" & Rows.Count).End(xlUp).Row).ClearContents

 

Hope that helps

Let me know how you get on

 

Thanks

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco

Need I say it? (but I will anyway) - it works perfectly!

 

But better than just "works", it has totally transformed the run-time. The original coding copied the RV/FO values across to col H or L one at a time. A 500 row sheet took about 2 minutes to fill each column and I'd figured that before I moved on to the sheets that have thousands of rows I should go and warm up the coffee machine. Now the transfer is instant. One click and Bingo!, the column is populated. Admittedly I've only tested it on the sheet that I sent you, so only 40 rows, but the difference was instantly apparent and I'm assuming that the same will happen with the larger sheets. I'll test that shortly. Brilliant!

 

Now, I've had a play with the non-RV FO and I've managed to create what is, in effect, just a very expanded variety of the Q - AE columns.  "very expanded" means that the sheet will now grow by a further 60+ columns. As noted, the aesthetics are irrelevant so for the reasons you stated, let's stay with the proliferation of helper columns.

 

The coding concept will be identical except that here we'll only be transferring a single code ("FO"), so no need for any fuzzy lookup. I've already discovered that the transfer of just a single value means some minor code mods that are beyond my simple mind. I'm OK at simply changing column letters, but re-coding it to transfer one value instead of two is way outside my scope! I'll endeavour to create a sample sheet for you later today.

 

Thanks once again for your help with this.

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco
I'll email you the updated workbook shortly, but we're now in the home straight - just one last imposition on your time.

You may have gathered from the earlier post that my gut feeling re the non-RV FO values proved to be correct and there are many FO occurrences that your coding simply can't detect. After a bit of thought I realized that I could fairly readily create new blocks of formulae similar to those already in place, to generate the missing FO values, and that it should then be a fairly easy task to adapt your coding to accommodate these new formula blocks.

The requirement with the new blocks is to:
a)  Copy the horizontal occurrences of "FO" from col AH - BK and write them vertically in col I; and
b)  Copy the horizontal occurrences of "FO" from col BN - CQ and write them vertically in col M
following the same sequencing method as with the existing coding.
It's conceivable that subsequent rows may try to write to a cell in col I or M that already contains FO, so you may need to protect against an error occurring here? At least this time there's no conflict between RV and FO being written to the same cell.

If there's any coding advantage in having the blocks of SH formulae contiguous and the blocks of SL formulae contiguous, feel free to rearrange the columns in my current layout to suit. Column Q and beyond is your territory; my interest is in col A - O.

We're so close to completion that I've already moved back to the main worksheet and am busily setting it up to take the data from col H - O of the back-test sheet. I'm looking forward to then testing the output from the main sheet against my manually determined output. I'm hoping for no mis-alignment, although I'm fully expecting the coding to show up errors in my manually determined output, and this is one of the reasons for wanting to automate the number crunching. Speed and accuracy - computers fill that role so much better than the human brain, especially my one!

And again, in anticipation - thank you so much for your kind assistance with my little project.

 

Share this post


Link to post
Share on other sites
  • 0

Hi Oldfella, 

 

I found the bug in the code, maybe I had one too many beers whilst coding :D 

Your requirement was:

Quote

c) An FO adding to an RV copied from a preceding row replaces the RV; and

 

I obviously misread this and wrote the code below. 

ElseIf sSeq = "FO" And sOutput = "RV" Then
                    sOutput = "RV"

please replace it with the correct output here:

ElseIf sSeq = "FO" And sOutput = "RV" Then
                    sOutput = "FO"

This should fix the issue you found, please let me know. 

 

I'm going to start work on the new blocks you have specified and will have some code for you by tonight. 

 

Cheers,

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco

Sorry about the blank column and the consequent re-code. The lack of formula in SH +1 was an exercise in convenience (laziness?) for me, resulting in new problems for you. If you need a challenge, I'm your 'Go To' man!  You may recall that I mentioned in one of the posts or emails that the SH/SL values are peak/trough values for S, so I figured there was no point in coding SH +1 to look for a value that exceeded the SH column value as by definition SH +1 cannot exceed SH as it would mean that SH is not a peak. Seemed like a good idea at the time  :D

I have to go out for a while; when I come back I'll apply the new code and let you know the outcome. Could it be that we're suddenly at the end of the exercise???

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco
WtF???  We're there!  After sorting a couple of minor hiccups, we have fait accompli!!!


First minor issue was that the second sub-routine was calling the wrong function. You'd simply forgotten to update the function name, so I fixed that readily enough.


The second issue, and there were two instances of it, was that the new coding was writing FO to col I and M one row above where the same FO had been written in col H and L. An FO occurring, for example, in SL +2 was writing in col M to the SL +1 position. That too had a nice easy fix - I simply added in two new, blank columns, 1 in each block, to push the ocurrences down by 1 row. I've emailed you a copy of the updated workbook so you can see the changes and the end result.


The final embellishment was to combine the 4 x sub-routines into one, and then assign the new sub to an 'Update' button on the worksheet.


To put your work of art into practice, all I have to do now is enter new blocks of data into col B - G, hit 'Update', then make use of the output from col H - O.


Caleeco, thank you so, so much for your time and your skills. It has taken me a year of back-testing to derive what, to you, must look like an incomprehensible assortment and inter-relationship of meaningless symbols. Following the same manual procedure as used last year, the back-testing that I still have ahead of me would also have taken about a year - maybe more. I still have some fine-tuning to complete on the main worksheet but once that's complete - hopefully over the weekend - I anticipate the testing to now require no more than a month. Brilliant!!

vzaay7rnwxy2rrun.jpg       hvh9fc00h2erm8b0.jpg     railvxmtnkrgrq1y.jpg      n0fmjgkeivap073s.jpg

The above are extremely well-deserved. Maybe one day I'll get the opportunity to convert them into real, live, cold and wet ones.

 

Best wishes and grateful thanks from a very satisfied OldFella

 

  • Thanks 1

Share this post


Link to post
Share on other sites
  • 0

Well that's some excellent news! :D Apologies for the obvious errors I had left, but great to see you figured out what was wrong and corrected them! Good idea of having them run off a single button click  :) 

 

Wow! That's sounds like quite a project. Glad I was able to help shorten the lead time on completion & hopefully remove hours of manual data entry! (it's what VBA is built for!)  Haha, thanks for the beer... it's made me thirsty, so going to crack open a real one :D Enjoy your weekend!

 

Please mark as solved if you're happy with the current solution ;) 

 

I'm off to your other thread to see if I can figure out that error you've been getting. 

Cheers

Caleeco

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×