Fill a sheet with data from three different sheets, same workbook, in a very tricky way

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have this challenge here again.

I am pulling data from three sheets: "Group 1", " Group 2" and "Group 3".

I am not with my PC ATM so I used a sketch to proof my case.

My goal is to have the data from all three sheets displayed on another sheet as shown here
20200113_231540-1.jpg


So, taking item X for example, we should have something like this:

15789584681761972118019.jpg


This is how the data set is for each sheet, group 1 to 3:
20200113_231640-1.jpg


So here, let us assume the database for group 1 to 3 start from column A where row 1 is a header.

And the sheet we are filling also start at same column A, row 1 being a header.

Since the sheet tabs have the group 1 to 3, while checking data on group 1, I want that group 1 appear as shown in the first or second image.

Then after that we check for group 2, leaving a blank row in between.

Then we repeat for group 3.

From the image 2, it shows what happens if only group 1 has data. And only two recorded events.

I hope this my explanation could get me a hit.

Thanks and please let me know if more details are required..
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi Kelly,
I like your scetches, but am wondering what you are looking for. VBA code maybe? Or a workbook with formulas? Did you already make a prototype (if so, e.g. a dropbox link would be helpful)?
Cheers,
Koen
 
Upvote 0
Hi Kelly, I like your scetches, but am wondering what you are looking for. VBA code maybe? Or a workbook with formulas? Did you already make a prototype (if so, e.g. a dropbox link would be helpful)? Cheers, Koen

Thanks, Koen, for the concern. I came up with the prototype after I got close to my PC. I have not posted it here yet because I am having problem with my network ATM. I will do so ASAP, when my network is stable. I can only manage to post text ATM.

And as to what I really want? That's a VBA solution. The prototype I have is with formulas.

I will get the link here ASAP
 
Upvote 0
Hi Kelly, I like your scetches, but am wondering what you are looking for. VBA code maybe? Or a workbook with formulas? Did you already make a prototype (if so, e.g. a dropbox link would be helpful)? Cheers, Koen

Here is the workbook:

 
Upvote 0
Upvote 0
Got it solved at excelforum by @Marc L

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim K, H, L, N&, V, R&, C%
        If Target.Address <> "$B$1" Then Exit Sub
        Application.EnableEvents = False
        Me.UsedRange.Offset(1).Clear
        If IsEmpty(Target) Then Application.EnableEvents = True: Exit Sub
        K = [{2,3,4,15}]
        H = Application.Index(Worksheets(1).UsedRange.Rows(1), , K)
        L = Application.Index(Worksheets(1).UsedRange.Rows(1), , [{16,16,17,17}])
    For N = 1 To Me.Index - 1
        With Sheets(N).UsedRange
                V = Application.Match(Target, .Columns(1), 0)
            If IsNumeric(V) Then
                Cells(R + 2, 1).Value2 = .Parent.Name
                Cells(R + 3, 1).Resize(, UBound(K)).Value2 = H
                R = R + 4
                Cells(R, 1).Resize(, UBound(K)).Value2 = Application.Index(.Rows(V), , K)
            For C = 5 To 13 Step 2
                If IsEmpty(.Cells(V, C)) Then Exit For
                R = R + 1
                Cells(R, 2).Resize(, 2).Value = .Cells(V, C).Resize(, 2).Value
            Next
                R = R + 1
                L(2) = .Cells(V, 16).Value2:  L(4) = .Cells(V, 17).Value2
                Cells(R, 1).Resize(, UBound(L)).Value2 = L
            End If
        End With
    Next
        Application.EnableEvents = True
End Sub
 
Upvote 0
Got it solved at excelforum by @Marc L

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim K, H, L, N&, V, R&, C%
        If Target.Address <> "$B$1" Then Exit Sub
        Application.EnableEvents = False
        Me.UsedRange.Offset(1).Clear
        If IsEmpty(Target) Then Application.EnableEvents = True: Exit Sub
        K = [{2,3,4,15}]
        H = Application.Index(Worksheets(1).UsedRange.Rows(1), , K)
        L = Application.Index(Worksheets(1).UsedRange.Rows(1), , [{16,16,17,17}])
    For N = 1 To Me.Index - 1
        With Sheets(N).UsedRange
                V = Application.Match(Target, .Columns(1), 0)
            If IsNumeric(V) Then
                Cells(R + 2, 1).Value2 = .Parent.Name
                Cells(R + 3, 1).Resize(, UBound(K)).Value2 = H
                R = R + 4
                Cells(R, 1).Resize(, UBound(K)).Value2 = Application.Index(.Rows(V), , K)
            For C = 5 To 13 Step 2
                If IsEmpty(.Cells(V, C)) Then Exit For
                R = R + 1
                Cells(R, 2).Resize(, 2).Value = .Cells(V, C).Resize(, 2).Value
            Next
                R = R + 1
                L(2) = .Cells(V, 16).Value2:  L(4) = .Cells(V, 17).Value2
                Cells(R, 1).Resize(, UBound(L)).Value2 = L
            End If
        End With
    Next
        Application.EnableEvents = True
End Sub

Hi,

Can someone here explain to me the meaning of the various lines in the code above?

I have asked the creator of it to explain it but has not responded yet.

I will be very grateful to have it explained .

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top