• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

macro for unmerging data and replicating some data

hello. Can someone of you kind folks please provide some help with following?
I need a vba code in order to convert raw data into specific layout (w some replication of records)
Sample of raw data is shown in range A:Q
Desired output of vba code is shown in range S:Ai
The raw data could be up to 80,000 rows

Ideally one sheet would have the extensive raw data, and the VBA code should place the 'revised databank' into a different sheet
VBA code should unmerged specific cells, and replicate data (in a downward basis, & just for those records in which the merging implied "summarized presentation" )

I am very beginner about vba code so please kindly let me know where the code should be placed/saved and how to run it
I kindly appreciate anyones help as previous occasions. I hope you all are healthy and stable. Please advice at any oportunity possible.
Thanks really!
 

Attachments

BobBridges

Active Member
I didn't study your sample in detail, but it looks like what you want is to have a program look down cols 1 through 10 and if the cell is merged:
1) If it's in col 7 and the value is "Total", unmerge it and copy "Total" to the empty cells at the right
2) If the value is not "Total", unmerge it and copy the "master" value over each of the new cells underneath it.

Is that right? Or are there complications I missed?
 
hello BObo. yes, that is aceptable criteria (but also something like that should happen for col 1 thru 6; i mean.- unmerge and copy master value below). let me know when chance. much gracious eh
 

BobBridges

Active Member
I'm more about teaching you how to program than just doing it for you. You say you're a beginner, but do you at least know how to write statements into the VBA editor? If so, I've written this much, which you should enter in a module and see how much of it you can understand. Be sure to ask questions. When you can tell me how it works, we'll add to the program.
Code:
Sub Main()
  Set ows = ActiveSheet
  Set ocs = ows.Cells

  For jr = 2 To 20 'loop through the top 20 rows.
    For jc = 1 To 10 'loop through the first 10 columns
      Set oc = ocs(jr, 1)
      If Not oc.MergeCells Then GoTo IterateCell 'skip this cell if it's not merged

      ' Unmerge the cell and get ready to copy its value.
      Set org = oc.MergeArea 'save the merged area
      oc.MergeCells = False 'turn off merge for this cell
      vm = oc.Value 'save the master value
      f1 = True 'we're going to skip the first cell (which is itself, so to speak)

      ' Copy the master value over all the new cells.
      For Each ocm In org.Cells
        If f1 Then f1 = False Else ocm.Value = vm 'except the first (ie the master) cell
        Next ocm

      ' This is just a first test; we'll look at just the first merged cell.
      MsgBox "Found merged cell at " & oc.Address
      Exit Sub

IterateCell:
      Next jc
    Next jr
  End Sub
 
thanks so much. no, i do not know how to write code. i apologize if i misinform about my lack of vba knowledge.
please do let me know if you could complete the code you kindly shared.
again, my apologies. Again, thanks for taking time to aid me before.
 

BobBridges

Active Member
No, I won't write it, but I'm willing to teach you how. Do you want to make the effort to learn, or would you rather someone just do it for you? Someone around here probably will; there are lots of helpful folks on-line.
 
thank you. it is not that i wont make effort , the problem is i am multitasking because of short staff and am looking to save some time by reaching out to someone in this forum and have that particular help with the vba code.
i need to work allnighter to have a report ready, and thats the reason for my original post
i genuinely thank you for your assistance and i hope i dont give you wrong impression. God bless you.

could anyone please try to help me with the original post? it would be a lifesaver truly!!
 
hello marc L. yes, pleae assume that the orginal raw data with merged cells would be on a sheet called "Sheet 1" on a separate excel workbook.
That workbook is ran by somebody in the office and sent to me
So i need a macro that can 'convert' the contents of that file/sheet 1.
The output could be placed in a new sheet within the same file.

thanks for your msg and let me know when a chance. I anxiously wait for feedback. thanks again for lending a hand

ps: ignore the pivot table in the example file i attached.
 

Marc L

Excel Ninja
So obviously the easy way is to ask the sender to create the workbook without merging cells …​
 
what i meant is that i receive the file from someone else; but that person is not preparing the data. The sample i attached originally showed an example of about 100 rows of data. But the normal file is comprised of about 40,000 to 60,000 rows of data the system generates in such format.
That is why i thought of going into this forum to see if macro could aid me. As it is very impractical doing the unmerging and copy-paste manually
please let me know if you might have a chance to help with macro. I sincerely thank you for taking time to comunicate earlier.
 

Marc L

Excel Ninja
But the normal file is comprised of about 40,000 to 60,000 rows of data the system generates in such format.
Ask the user of this 'system' to generate an appropriate Excel workbook as it should be faster rather than any Excel VBA procedure​
'cause for 40 000 to 80 000 rows it could take a while to unmerge data …​
 

BobBridges

Active Member
But as he already said, Marc, the person he gets the data from isn't the one who generates the data; if I'm reading him correctly, he's trying to tell us that he has no control over the format.
 
hi. yes Bob. unfortunately we have no control over the format nor the output layout/structure.
Sorry for being overbearing, but can anyone help please? Is there some way thru macro, to 'convert' the data as the sample i shared?

please kindly let me know if i should stop requesting any asistance, in case there is no way macro could do the solution
im running out of time and doing a manual process would be such such stressing and exhausting task

thanks for any help , might be possible. take care
 

Marc L

Excel Ninja
Where the VBA procedure should be located (aka in which workbook) ? How it should work ?​
The more accurate is your answer, the less mod you will have to proceed within the code …​
 
thank you Marc
let me know if this is clear
a) i could manually copy the raw databank in a sheet called "original" within a workbook that contains the VBAprocedure
(note= usually there are close to 80k rows of data ; though...i pasted about 150 rows of examples in the sample file originally attached)

b) if you can look at the sample file, the procedure should do the following (placing the converted data into a sheet that can be called "converted")

first, copy-paste original data (in sample file: records in cols A thru Q) into "converted" tab and after, do an unmerge of cells

2nd, in the "converted" tab....after the unmerging, and for each of the first 12 columns (in sample file, these are --from left to right; col A thru L), the data in each column should be copied over each of the new cells (or rows) underneath each
(the copy-paste task should be for each piece of data of each aforementioned column. It should be for all blanks cells but not overwrite any 'new record' identified in the next available non blank cell or row)

special note: in col 7 if the value is "Total", after unmerge it , then copy "Total" to the empty cells at the right (the value is normally exsting in col7 and thru col 12, of the original data in range col A thru Q)

the 'converted' tab should look like the sample file.- the range of columns S thru AI,
where the bold lettered values in columns S thru AC, is a way to highlight the values that were copy-pasted thru the vba procedure.

please kindly let me know if any clarification
I REALLY REALLY THANK YOU for your patience and willingness to help me.
Bless you
 

BobBridges

Active Member
Oh, a VBA program can definitely do it. But as I said, I'm all about teaching a man to fish. If you're too busy to learn, maybe someone else could be assigned to learn it? Then that person would be able to do other programming tasks as well.
 

Marc L

Excel Ninja
According to the attachment a VBA demonstration for starters to paste only to the Sheet 1 worksheet module :​
Code:
Sub Demo1()
   Const S = "Converted", T = "Total"
         V = [A1].CurrentRegion.Value
    For R& = 2 To UBound(V)
        If IsEmpty(V(R, 1)) Then For C% = 1 To 6: V(R, C) = V(R - 1, C): Next
        If IsEmpty(V(R, 7)) Then For C = 7 To 10: V(R, C) = V(R - 1, C): Next
        If V(R, 7) = T Then
            For C = 8 To 12:  V(R, C) = T:  Next
        ElseIf IsEmpty(V(R, 11)) Then
            V(R, 11) = V(R - 1, 11)
        End If
    Next
        If Evaluate("ISREF(" & S & "!A1)") Then Sheets(S).UsedRange.Clear Else Sheets.Add(, Sheets(Sheets.Count)).Name = S
    With Sheets(S).[A1].Resize(UBound(V), UBound(V, 2)).Columns
        For C = 1 To .Count:  .Item(C).NumberFormat = Cells(3, C).NumberFormat:  Next
       .Value = V
       .AutoFit
    End With
        Application.Goto Sheets(S).[A1]
End Sub
From the worksheet use the keys combo Alt F8 to launch the VBA procedure or just add a button …​
Do you like it ? So thanks to click on bottom right Like !​
 
Marc, THANKS thanks THANKS!!!
i will test the code today morning ; i will let you know if any problems

ps:would this code work optimy, w/ handling lots of records (btwn 40k and 80k) ??
 
@Marc L ....code worked great!
i made a test with aproximately 55,000 rows of data and the converted data looked apropiately structured and key master values replicated in cells below each master record
many many thanks from the bottom of my heart. bless you.
 

Marc L

Excel Ninja
Thanks. My demonstration works with a memory array rather than unmerging / filling blank cells …​
 
thanks a lot again. The macro will save us an enormous amount of time , making reports out of data that is contained in the original merged sheet (and future sheet/databanks).
have a good weekend. Thanks once more
 
Top