• 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.

multiple table in one worksheet into one master table using userform

L

ladydee

New Member
Hi, i'm new in using excel macro and vba. In my workbook, there are large amount of uncluttered data in many table in one worksheet. I need help how to combine/merge/join multiple table in one worksheet into one master table into another worksheet using userform? i'm sorry if my explanation is confusing because I'm not so good in English. thank you for any help.
 
Hi, I'm sorry. here i attach sample for my question. Is there any code/script i can use for merge/join/combine multiple tables in same worksheet:

Capture.PNG

into one master table:

Capture1.PNG

one of my option is using userform like this:

Capture2.PNG

or any other suggestion? thank you for help.
 
@ladydee ... hmm
It would be much 'easier for You' to send an Excel-file and
explain how so You would like to combine.
So far, no need any userform if You cannot tell 'where'!.
 
Hi, Sorry. here i attach sample file for this question. In sheet1, contains many/multiple tables with different no of column, only have same column for Id and Name. I want to combine/join/merge all tables in sheet1 to be one master tables(can be refer in sheet2). is there any vba code/script i can use for this? thank you.
 

Attachments

  • Book1.xlsx
    12.2 KB · Views: 2
Hi vletm,

Thank you for your help. I test your code and it work. Can you explain a little about this code? How about if I want use it for other workbook and there are about ten tables in sheet1? I'm sorry for asking this because I really new in using excel vba. Thank you again for your help. :)
 
ladydee - oh :)
Explain ... 'highlites':
- makes new Table to Sheet2
- works Table-by-Table
- collect row-by-row data and sets data in correct row/column
-- finally sorts Sheet2's table
Other workbook:
As I wrote:
1) Those three tables with those names have to be in 'Sheet1'
2) There have to be 'Sheet2' which uses columns 'A:H'
... about ten:
1) it should to know how many tables (For tbls = 1 to 3)
2) tables should have names as in that file
3) tables should have same 'columns' as in that file
4) it is possible then ... everything match!
Really new:
Everyone has always first steps - start with small steps!
>> If You need help - just ask
 
okay thank you for the explanation. I wanna ask, If there are more than 10 tables, Do I need to make any change or need make any added? I kind of confuse about the part of below code.

Code: 
                    If Err.Number = 0 Then
                        datas(1, xx) = .Cells(0, x)
                        datas(2, xx) = .Cells(y, x)
                    Else
                        If .Cells(0, x) = datas(0, 0) Then
                            If .Cells(0, 5) = datas(0, 7) Then
                                datas(1, 7) = .Cells(y, 5)
                            Else
                                datas(1, 8) = .Cells(y, 6)
                            End If
                        Else
                            datas(1, 5) = .Cells(y, 5)
                        End If
                    End If
                    If Left(.Cells(0, x), 6) = "System" Then
                        datas(2, xx) = "YES"
                        If tbls > 1 Then datas(2, xx) = .Cells(y, 3)
                    End If
                Next x
                With Sheets("Sheet2").Range("LadyDee")
                    y_max = .Rows.Count + 1
                    If tbls = 1 And y = 1 Then y_max = y_max - 1
                    ADD_NEW = True
                    For yy = 1 To y_max
                        If .Cells(yy, 1) = datas(2, 1) And .Cells(yy, 2) = datas(2, 2) And _
                            .Cells(yy, 3) = datas(2, 3) And .Cells(yy, 4) = datas(2, 4) Then
                            If tbls < 3 Or (tbls = 3 And .Cells(yy, 8) = Empty) Then
                                For x = 1 To 8
                                    If datas(2, x) <> Empty Then .Cells(yy, x) = datas(2, x)
                                Next x
                                ADD_NEW = False
                                yy = y_max + 1
                            End If
                        End If
                    Next yy
                    If ADD_NEW Then
                        For x = 1 To 8
                            If datas(2, x) <> Empty Then _
                                .Cells(y_max, x) = datas(2, x)
                        Next x
                    End If
                End With
                For yy = 1 To 2
                    For xx = 0 To 8
                        datas(yy, xx) = Empty
                    Next xx
                Next yy
            Next y
        End With
 
ladydee
1) if different numbers of tables than three (3) then
You should change number three (3) to needed number of tables ex 111.
check below:
Code: 
    For tbls = 1 To 3
        With Sheets("Sheet1").Range("LadyDee_" & tbls)
2) also code requires that ALL Tables are named as in original code!
3) if 'rule' for ADD_NEW should changes ... then it should change!
4) if 'new columns' then ... there are more things to do.
 
oh lady lady ladydee dee
as I wrote ... there are more things to do.
Maybe it would be easier to try to write
which part of code won't need to change/add
all depends Your changes and
changes of logic!
With VBA, You have to tell just what You would like to happen - not something almost like.
 
I just want to understand how your code work, so I can make some changes by myself if needed. btw, Thank you so much for your help. I really appreciate your help. :)
 
Of course You can do it!
1) You have to know 'Result'-table; columns name's, locations.
2) You ready place for that
3) You make 'Result'-table with it's headers
4) do table-by-table
4a) solve locations of columns
4b) solve 'system'-variable changes
4c) compare is it totally new data or add to old
4d) fill data
5) finally sort 'Result'-table if needed
You just remember to do it step-by-step,
everything have to match,
one extra (or missing) character can change everything (or not).
And then You'll got that "Done"
... You gotta manually check everything again.
 
You must log in or register to reply here.
Back
Top