Group 4 data sets into one
Hi,
I have 4 data sets as shown below.
1.png
I want to merge all four data sets such that the end result is grouped and sorted based on Surname.
My desired result is as shown below.
2.png
I have attempt to generate the result, however it does not "spill" and it fails to pick up value 2,3,4.
I've attached the sample workbook.
Thank you
Last edited by bob112233; 03-29-2023 at 04:52 AM. Reason: need further help
Delete ALL expected results.
Formula:
Please Login or Register to view this content.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Awesome!
Thanks once again Glenn!!!
Edit...
I just noticed, that they are not grouped.
All good, i sorted it
Last edited by bob112233; 03-28-2023 at 04:40 AM.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
@Glenn Kennedy,
I've tried your approach and modified it, however I can't seem to make it work based on my current data.
I've updated my excel workbook to show you a new set of data.
OK. I see the problem... not entirely fixed yet, becasue there may be a problem with your expected results...
See shaded cells. Which is correct? The original (and if so, why), my version 1 or my version 2???
Last edited by Glenn Kennedy; 03-29-2023 at 03:16 AM.
The original is correct, as ID 333 appears twice across value 1 and 2 in the same CODE. Hence the expected result.
I
Totally confused... because it ALSO occurs in value 4 in the same code.
To help clarify the solution, I want to group ID & CODE with Value 1,2,3,4.
We want to group those 4 data sets into 1 large data set.
For example, tracking ID 111 we can see it occurs in the following value tables, 1, 2 and 4.
Grouping them would look something like this.
ID Surname First Name CODE Value 1 Value 2 Value 3 Value 4
111 aaa aaa xxx 70 1 0 70
Another example for ID 555 we can see it occurs in the following value tables 1,2,3,4 but in different codes. So grouping them will look something like this.
ID Surname First Name CODE Value 1 Value 2 Value 3 Value 4
555 eee eee hhh 86 0 0 0
555 eee eee abc 0 5 0 0
555 eee eee xxx 0 0 5 0
555 eee eee yyy 0 0 0 5
I hope this helps...
I noticed, I made a mistake in my desired results.
I will update my post, i can see the confusion now.
Here is the image with the desired results.
des results.png
I;ve updated the original post to include the workbook with the updated desired results.
Last edited by bob112233; 03-29-2023 at 04:21 AM.
Is ther still a mistake ?
What happened to
555 eee eee abc 5
you are right... its missing
I've updated it lol .
It will be a bit messay (I think) if I try to do it ALL in one formula.
Try this, however. Two dynamic arrays. I decided NOT to assume that the data blocks are beside each other.
Adjust the ranges to suit your real data,. They do not have to match the exact number of rows.
But... I tried and here it is:
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks