auto sort

T

ttowncorp

Board Regular
Joined
Feb 2, 2015
Messages
187
Office Version
  1. 365
Platform
  1. Windows
this will be a major help but i'm not sure if anybody can help me. I have a massive data sheet that i would like to sort out. I dump the raw data on sheet two and would like to type in the item i want to see the history on in sheet one. example below sheet two then sheet one.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ticket id[/TD]
[TD]priority[/TD]
[TD]date of service[/TD]
[TD]vehicle id[/TD]
[TD]user name[/TD]
[TD]department[/TD]
[TD]status[/TD]
[TD]problem[/TD]
[TD]corrective action[/TD]
[TD]service date[/TD]
[TD]comments[/TD]
[TD]compliance[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]no[/TD]
[TD]5/1/19[/TD]
[TD]3201[/TD]
[TD]robert[/TD]
[TD]southern[/TD]
[TD]completed[/TD]
[TD]tires[/TD]
[TD]replaced tires[/TD]
[TD]5/1/19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]yes[/TD]
[TD]5/2/19[/TD]
[TD]2200[/TD]
[TD]bob[/TD]
[TD]western[/TD]
[TD]active[/TD]
[TD]piston 4[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]no[/TD]
[TD]5/2/19[/TD]
[TD]2200[/TD]
[TD]bob[/TD]
[TD]western[/TD]
[TD]completed[/TD]
[TD]tires[/TD]
[TD]replaced tires[/TD]
[TD]5/2/19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]no[/TD]
[TD]5/2/19[/TD]
[TD]2200[/TD]
[TD]bob[/TD]
[TD]western[/TD]
[TD]completed[/TD]
[TD]oil change[/TD]
[TD]replaced oil[/TD]
[TD]5/2/19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]2200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ticket id[/TD]
[TD]priority[/TD]
[TD]date of service[/TD]
[TD]user name[/TD]
[TD]department[/TD]
[TD]status[/TD]
[TD]problem[/TD]
[TD]corrective action[/TD]
[TD]service date[/TD]
[TD]comments[/TD]
[TD]compliance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]yes[/TD]
[TD]5/2/19[/TD]
[TD]bob[/TD]
[TD]western[/TD]
[TD]active[/TD]
[TD]piston 4[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]no[/TD]
[TD]5/2/19[/TD]
[TD]bob[/TD]
[TD]western[/TD]
[TD]completed[/TD]
[TD]tires[/TD]
[TD]replaced tires[/TD]
[TD]5/2/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]no[/TD]
[TD]5/2/19[/TD]
[TD]bob[/TD]
[TD]western[/TD]
[TD]completed[/TD]
[TD]oil change[/TD]
[TD]replaced oil[/TD]
[TD]5/2/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
to have A2 through K2 on sheet1 not resize when it's pulling data from sheet2 and dumping it on sheet1. I figured out how to move the Target from F1 and made it H1 so it looks more centered. :)
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Try modifying the line in red, as below:-
Code: 
With Sheets("Sheet1").Range("A3").Resize(c, 11)
     Set Rng = .Parent.Range("A3", .Parent.Range("A" & Rows.Count).End(xlUp))
    [COLOR="#FF0000"][B] If Not Rng.Row = 2 Then Rng.Resize(, 12).ClearContents
[/B][/COLOR]    .Value = Application.Index(nRay, Evaluate("Row(1:" & UBound(Ray, 1) & ")"), Array(1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12))
    .Columns("C:C").NumberFormat = "dd/mm/yyy"
    .Columns("I:I").NumberFormat = "dd/mm/yyy"
    .Borders.Weight = 2
    .Columns.AutoFit
End With
 
Upvote 0
thanks for taking your time in helping me out. I've made the changes but did not see a effect on the sheet. what i do notice is that if i type in a incorrect vehicle number I get a "run-time err 1004" and keeps point to this line of code highlighted in yellow With Sheets("Sheet1").Range("A3").Resize(c, 11) not sure why it's pointing to that since that has not been modified. i would like to send you my sheet so you can see what i'm talking about. i put it in my drop box. and again thanks

https://www.dropbox.com/s/q6z17o8e1wi2iu0/new auto sort program testing 6 demo.xlsm?dl=0
 
Upvote 0
Can you send that file using "Box.com", I'm not able to download that file.

When you spoke about "Resize" were you referring to row 2 being deleted when the code was run or something else???
Please explain !!!

NB: I think that "Runtime Error 1004" is because the variable c has a value of 0. This should not be the case, because even if you enter a invalid number, c will always be "1" because that represents the header row. Even will an invalid number you should always see the headers in row 3. This is because the Code looks specifically for the Header name "vehicle id", if that header is not there, then c could be "0" and you would get the error. !!!!
 
Upvote 0
oh yes I have added the error message code in it as well but since the code before it is not working i cant get it to start. if a vehicle ID is inputed in that's not in sheet two that error message should pop up with the text i created.
 
Upvote 0
Try this:-
I've remove the code part that copies the headers so the results start "A3" and also altered the error trapping so if you enter an incorrect number (i.e. value of "c" is 0). I've also removed the "Column "Autofit". That might help.
As a result you will need to have your row 2 headers pre set, which I think they are already.
I found that the "H1" number changed font size to cover "H2" if the Font size was set at 72, but did not if set to 48 ????.
NB:- Apart from cell "H1" and the columns Autofit, I did not find any other Resizing problems.
Code: 
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "H1" [COLOR="Navy"]Then[/COLOR]
ray = Sheets("Sheet2").Range("A1").CurrentRegion
ReDim nRay(1 To UBound(ray, 1), 1 To UBound(ray, 2))
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(ray, 1)
        [COLOR="Navy"]If[/COLOR] ray(n, 4) = Target.Value [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            [COLOR="Navy"]For[/COLOR] ac = 1 To UBound(ray, 2)
                [COLOR="Navy"]If[/COLOR] IsDate(ray(n, ac)) [COLOR="Navy"]Then[/COLOR]
                    nRay(c, ac) = CDbl(DateValue(ray(n, ac)))
                [COLOR="Navy"]Else[/COLOR]
                    nRay(c, ac) = ray(n, ac)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] ac
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
Temp = Target
[COLOR="Navy"]If[/COLOR] c > 0 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1").Range("A3").Resize(c, 11)
     [COLOR="Navy"]Set[/COLOR] Rng = .Parent.Range("A3", .Parent.Range("A" & Rows.Count).End(xlUp))
     Rng.Resize(, 3).ClearContents
    .Value = Application.Index(nRay, Evaluate("Row(1:" & UBound(ray, 1) & ")"), Array(1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12))
    .Columns("C:C").NumberFormat = "dd/mm/yyy"
    .Columns("I:I").NumberFormat = "dd/mm/yyy"
    .Borders.Weight = 2
    '[COLOR="Green"][B].Columns.AutoFit[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Else[/COLOR]
MsgBox UCase(Sheet1.Range("H1")) & " No data has been entered for this bus at this current time.", vbCritical, "ALERT"
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
This is beyond perfect!!! thank you so much for your hard work in this coding and your patience, this thing works awesome now. Maybe later down the road I have a old project from yrs ago I been trying forever to get it to work, well it works but at times it lags, but that's for another time. but that you again friend. :rofl::rofl::rofl::rofl:
 
Upvote 0
i hope all is well. I have been using the sheet for some time now and it's working great. I would like to add to more rows for display in the sheet and for the most part i thought all i needed to change .Value = Application.Index(nRay, Evaluate("Row(1:" & UBound(ray, 1) & ")"), Array(1, 2, 3, 4, 13, 6, 7, 8, 9, 10, 11, 12)) into .Value = Application.Index(nRay, Evaluate("Row(1:" & UBound(ray, 1) & ")"), Array(1, 2, 3, 4, 13, 6, 7, 8, 9, 10, 11, 12, 13, 14)) is there anything else i need to change so the other columns will populate like the rest
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Help needed with creating a macro or function for finding specific keywords & then move to Sheet2
Replies
2
Views
494
kevin9999
K
HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
312
Jeffrey Mahoney
Jeffrey Mahoney
A
  • Question Question
Fetch heads up stats between 2 teams
Replies
2
Views
281
AYouQueTai
A
I
  • Solved
Look for Cell Data in Cell Array, and Cell Data in Row Array and if match show this Cell Data
Replies
4
Views
246
Iccreamann21
I
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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