switching rows to columns and removing multiples with variable row count

fiscot

New Member
Joined
Mar 2, 2017
Messages
2
Hello,
I would love o get some help with this problem, since it was quite some time i used excel for more than summing things up.

So - I've got a table with about 1000 rows, and four columns.

The first column has the ID of a place.
The second column has the name of that place
The second column has a date of when the place was inspected
The third column has the 0-5 verdict of the place

The problem is that each place are inspected various times so that some places have been inspected 4 times, and some 0.

This means that the table might look something like this:
Place
Name
Date
Verdict
521
First place
2015.02.20
3
521
First place
2016.01.15
3
521
First place
2017.03.01
4
622
Second place
2014.02.10
2
622
Second place
2017.01.10
4
723
Third place
2016.08.10
1

<tbody>
</tbody>

I need to get the last three times the places were inspected, what date and verdict laid out in columns, how do I do that?

How do I get that table to look like this:

Place
Name
1st
Verdict
2nd
Verdict
3rd
Verdict
521
First Place
2015.02.20
3
2016.01.15
3
2017.03.01
4
622
Second Place
2014.02.10
2
2017.01.10
4
723
Third Place
2016.08.10
1

<tbody>
</tbody>

Thanks for any help.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Is this a one off report? Or will you be adding to the list, ie
What happens if Place 521 gets inspected again (this will be it's 4th time) let's say on 2017.04.01 and gets verdict 2 ?
Will that get added to your original list. If so
all the dates shift left by one shouldnt they, to reflect the last 3 inspections, ie should it now look like this?

Code:
Place Name        1st        Verdict 2nd        Verdict 3rd        Verdict
521   First Place 2016.01.15       3 2017.03.01       4 2017.04.01       2
 
Last edited:
Upvote 0
Dear Special-K99,
Thank you for your answer.

I will be exporting the data from a website, say once a month. So I can just regenerate a completely new report each time if that is easier. But you are correct in that shifting should occur if new inspections were to be added to the old report. It would be great to automate the process of importing the new values into an old sheet, but I'm just trying to solve the main problem first.

Best regards,
Fiscot
 
Upvote 0
Try this for results on sheet2.
Code:
[COLOR="Navy"]Sub[/COLOR] MG03Mar01
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Q [COLOR="Navy"]As[/COLOR] Variant, txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
    Dic.CompareMode = vbTextCompare
        ReDim ray(1 To Rng.Count, 1 To 4)
ray(1, 1) = "Place": ray(1, 2) = "Name": ray(1, 3) = "1st": ray(1, 4) = "Verdict"
n = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        ray(n, 1) = Dn.Value
        ray(n, 2) = Dn.Offset(, 1).Value
        ray(n, 3) = Dn.Offset(, 2).Value
        ray(n, 4) = Dn.Offset(, 3).Value
        Dic.Add Dn.Value, Array(n, 4, 1)
    [COLOR="Navy"]Else[/COLOR]
       Q = Dic(Dn.Value)
       Q(1) = Q(1) + 2
       Q(2) = Q(2) + 1
       [COLOR="Navy"]If[/COLOR] Q(1) > UBound(ray, 2) [COLOR="Navy"]Then[/COLOR] ReDim Preserve ray(1 To Rng.Count, 1 To Q(1))
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Q(1)
            [COLOR="Navy"]Case[/COLOR] 6: txt = "nd"
            [COLOR="Navy"]Case[/COLOR] 8: txt = "rd"
            [COLOR="Navy"]Case[/COLOR] Else: txt = "th"
        [COLOR="Navy"]End[/COLOR] Select
        ray(1, Q(1)) = "Verdict": ray(1, Q(1) - 1) = Q(2) & txt
        ray(Q(0), Q(1)) = Dn.Offset(, 3).Value: ray(Q(0), Q(1) - 1) = Dn.Offset(, 2).Value
        Dic(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(Dic.Count + 1, UBound(ray, 2))
    .Value = ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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