Look up DataBase and sorting

ttowncorp

Board Regular
Joined
Feb 2, 2015
Messages
178
Office Version
  1. 365
Platform
  1. Windows
=now() current date and timereported date#1234
12/20/16vehicle typepm datelast pm
ford11/15/1511/15/16
installed datereplacement date#times replaceds/np/n
rim12/11/1612/20/161mk312345
tire12/11/1612/20/161rj1522001
transmission
engine
seats
paint

<tbody>
</tbody>
I have been working on this basic data base for quite some time. but now it's getting to the point were their are just to much data to look at with out it being organized a little. i'll try to explain. I have been tracking parts from specific vehicles and would like to dump that data into a template I created to view a lot more simpler. I would like to be able to pull up that vehicle information by typing in it's number and it will pull up and sort out the information for that vehicle for that time. meaning on 12/20/16 vehicle 1234 will have different information then 1/16/16 vehicle 1234. my main sheet is called "DataDump" which I type in manually all the information into each cell for that vehicle and date. "Sheet4" is were my template is I created that I would like to dump all that info into and have it sorted out. here's what I have. mind you I added generic names like part 01 to help see what I been doing.DataDump is as long as from A1 to BH actually(the example at the bottom of this message). so when I have all my info added in my datadump and I go to my Sheet4 and type in the vehicle number in the cell that says [#1234] all those fields need to change to what the Reported Date says under [reported date]. if I change the reported date the fields should change for that specific date for that vehicle. does that make make any since? hope this can actually be created.

vehiclereport datepart01 part01 p/npart01 s/npart01 installed datepart01 replacement datepart02part02
p/n
part02
s/n
part02 installed datepart02 replacement datepart03part03
p/n
part03 s/npart03 installed datepart03 replacement datevehicle typepm datelast pm
123412/20/16tire12345mk312/11/1612/20/16rim22001rj1512/11/1612/20/16ford11/15/1511/15/16
232111/14/14rim22001rj151/13/1312/20/16Chrysler12/2/1712/2/16
123411/15/16transmission339201tran2311/10/1512/20/16ford11/15/1511/15/16

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

ttowncorp

Board Regular
Joined
Feb 2, 2015
Messages
178
Office Version
  1. 365
Platform
  1. Windows
this code at the bottom I use on another sheet for looking up and verify information, and it then drops it in appropriate rows and columns. how do I modify this code to work with what I'm trying to do in my first post above.

Option Compare Text 'ignore text case
Private Sub Worksheet_Change(ByVal Target As Range)
'******
Dim lastrow As Long
Dim lastcol As Long
Dim rng As String
Dim x As Long
Dim y As Long
Dim frng1 As String
Dim frng2 As String
cnt = 5 'first data row
lastrow = Sheet2.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lastcol = Sheet2.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
If Target.Address = "$D$1" Then
Sheet1.Range("A5:IV65536") = "" 'clear previous
rng = "A1:A" & lastrow
If IsNumeric(Application.Match(Sheet1.Range("D1"), Sheet2.Range(rng), 0)) Then
For x = 1 To lastrow
If Sheet2.Cells(x, 1) = Sheet1.Range("D1") Then
Sheet1.Cells(cnt, 2) = Sheet2.Cells(x, 2) 'id
Sheet1.Cells(cnt, 3) = Sheet2.Cells(x, 3) 'date
For y = 1 To lastcol
If y >= 4 Then
Sheet1.Cells(cnt, y) = Sheet2.Cells(x, y) 'other elements
End If
Next y
cnt = cnt + 1
End If
Next x
'sort
lastrow = Sheet1.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
frng1 = "B4:B" & lastrow
frng2 = "B4:E" & lastrow
Sheet1.Sort.SortFields.Clear
Sheet1.Sort.SortFields.Add Key:=Range(frng1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheet1.Sort
.SetRange Range(frng2)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'add issue count
For x = 5 To lastrow
Sheet1.Cells(x, 1) = x - 4
Next x
Else
MsgBox UCase(Sheet1.Range("D1")) & " No data has been entered for this bus at this current time.", vbCritical, "ALERT"
End If
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,140
Messages
5,599,964
Members
414,352
Latest member
macquarie_jchan58

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
Top