extract data from merge cells into single cells

[meir]

hello friends, i have a problem with a certain file,i can export data from my KM database into an excel file but the imported file that i get is in merged cells when there's a lot of blank cells in between and sometimes the data is spread into different cells
(the raw data is in sheet "main 1") ,what i need is to put the data from
"main 1" sheet into the table in "EXTRACT" sheet that only contains one raw to
any merged cell in the data sheet based on code reason column Q in "EXTRACT" sheet ,
i think it will only work with some kind of VBE code but i need help with that
thanks in advance

 

[SirJB7]

Hi, meir!
I don't read (nor write, nor anything else ) Hebrew, so I took the 1st cells with a lot of data (D5, G5), put them into Google Translator, and they appear to be just plain text. I couldn't find any merged cells. Could you please elaborate and indicate which merged cells are you referring too and which is the required output? Thank you.
Regards!

 

[meir]

hi,sirJB7
my end game is to put all data in lines 3 to 6 for example (in sheet "main 1",as you can see some cells in that raw are merged in some columm),into line 4 in the other sheet,the data need to match the data under the same
collum name in the source sheet(every columm for itself), the number of lines need to be taken from the source sheet each time is determined by the size of the merged collum P (CODE) in the source sheet,i need a VBE CODE
that do that to all the cells ofcourse.
i would welcome any help (whether or not you can read,write hebrew or anything ).

 

[SirJB7]

Hi, meir!
Could you please post a sample of the required output, for example for rows 3:6?
Regards!

 

[meir]

yes of course,iv'e added the required output for the first two merged rows (rows 3:8) to the file in the "extract" sheet,please note that some data in the raw sheat is sometimes spread into multiple cells across the desired rows(take cells H9-H29 for example,i think it need to have some kind of concatenate like formula in the code to solve this.

 

[SirJB7]

Hi, meir!

Give a look at the uploaded file.

I reset the display to left to right (so turn it on back from File, Options, Advanced, Show Options For This Worksheet) and I created a dynamic named range in worksheet EXTRACT:
NonMergedTable: =DESREF(EXTRACT!$B$3;;;CONTARA(EXTRACT!$B:$B);CONTARA(EXTRACT!$3:$3)) -----> in english: =OFFSET(EXTRACT!$B$3,,,COUNTA(EXTRACT!$B:$B),COUNTA(EXTRACT!$3:$3))

This is the macro code:

Option Explicit
 
Sub IHateMergedCellsForTheRecords()
    ' constants
    Const ksInputWS = "Main 1 "
    Const klInputRow = 3
    Const kiInputColumn = 1
    Const kiInputCode = 16
    Const ksOutputWS = "EXTRACT"
    Const ksOutputRange = "NonMergedTable"
    ' declarations
    Dim rngI As Range, rngO As Range
    Dim lInput As Long, lOutput As Long, iMerge As Integer, iColumn As Integer, iMergeX As Integer
    Dim sText As String
    ' start
    Set rngI = Worksheets(ksInputWS).Cells
    Set rngO = Worksheets(ksOutputWS).Range(ksOutputRange)
    With rngO
        If .Rows.Count > 1 Then Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
    End With
    ' process
    With rngI
        lInput = klInputRow
        lOutput = 1
        Do Until .Cells(lInput, kiInputCode).Value = ""
            ' number of max merged cells per item
            iMerge = .Cells(lInput, kiInputCode).MergeArea.Cells.Count
            ' new item
            lOutput = lOutput + 1
            ' main
            For iColumn = 1 To rngO.Columns.Count
                sText = ""
                iMergeX = 1
                Do While iMergeX <= iMerge
                    If sText <> "" Then sText = sText & vbCr
                    sText = sText & .Cells(lInput + iMergeX - 1, iColumn).Value
                    iMergeX = iMergeX + .Cells(lInput, iColumn).MergeArea.Cells.Count
                Loop
                ' new item column
                rngO.Cells(lOutput, iColumn - kiInputColumn + 1).Value = sText
            Next iColumn
            ' cycle
            lInput = lInput + iMerge
        Loop
    End With
    ' end
    Set rngO = Nothing
    Set rngI = Nothing
    Beep
End Sub

Now, your homework: please check the output since it'll be easier for you than for me who just "see" but don't "read". In case of any error indicate output cell address, input range address and correct output.

Regards!

PS: BTW, nice gesture that of yours of leaving a trailing space after 1st worksheet displayed name...

 

[meir]

thanks a lot :D it works like a charm,i am forever in your debt (and by the way,for the record,i also not to fond with merged cells ).

 

[SirJB7]

Hi, meir!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted... if you come without any merge stuff.
Regards!