Hello all,
I am trying to convert a matrix into three separate normalised table. I will have a lot of these and they will be bigger than the example so I'm hoping to find an automated method eg a module. Here is an example:
excel.jpg
Any help would be great!
Thank you.
Hello AndyRob1973 and Welcome to Excel Forum.
Here is a formula based proposal.
For the Activities table
For the ID: =IF(B8="","",SUM(A7,1))
For the Activity: =IF(B2="","",B2)
For the Visits table
For the ID: =IF(E8="","",SUM(D7,1))
For the Visit: =IFERROR(INDEX(C$1:F$1,ROWS(A$1:A1)),"")
For the Junction table
For the VisitID: =ROUNDUP(ROWS(A$1:A1)/MAX(D$8:D$12),0)
For the AcitityID: =COUNTIFS(G$8:G8,G8)
For the Required: =INDEX(C$2:F$5,MATCH(INDEX(B$8:B$12,MATCH(H8,A$8:A$12,0)),B$2:B$5,0),MATCH(INDEX(E$8:E$12,MATCH(G8,D$8:D$12,0)),C$1:F$1,0))
Note that conditional formatting could be used to hide zeros in the Required column.
For future reference, you will usually get faster responses if you include a sample Excel file rather than a screen shot. As you are new and as there was not too much data I produced an Excel file on this occasion.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you JeteMc,
That's superb! I'll test it against my actual data.
Thanks for the posting advice too.
I've attached the excel example in case anyone is able to give me a macro, but this will help me a lot
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks