Have information need to speed process

pfhmoney

New Member
Joined
Aug 6, 2006
Messages
25
I have a very basic question which I am not able to figure out the answer.

I have, on a regular basis, a VLook set up to look at a list of locations to return those particular locations user name. Sometimes I do not get to them daily, so on those days I am looking up multiple days worth of those Vlookups - I am wanting to come up with an additional step speed the process up returning the multiple day information. Example below of multiple day's info...

date 8/25 date 8/26

AL-PT01 167 AL-PT01 167
AR-0162 8756 AR-0162 8756
AR-0165 0385 AR-0165 0385
AR-PT01 5659 AR-PT01 5659
AZ-0213 0218 CA-0039 0433
CA-0042 0481 CA-0042 0481

what I currently have to do is then manually create this:

Loc # Loc Code Dates

AL-PT01 167 25, 26
AR-0162 9756 25.26
AR-0165 0385 25,26
AR-PT01 5659 25,26
AZ-0213 0218 25
CA-0039 0433 26
CA-0042 0481 25,26

Isn't there some way to do this with a formula?

Thank you for your help
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi pfhmoney,

If it were not for the "AZ-0213 0218 CA-0039 0433" case which must be split out to two rows it would be easy to do with a formula, but unfortunately this little twist makes it exceedingly difficult. However, it would be quite easy to do with a simple macro. Are you willing to entertain a solution that involves some macro (VBA) code?

Damon
 
Upvote 0
I would entertain a solution,and if macro is the solution...

Entertain away

The example that I gave you is in Excel... the information in columns but did not show up in here as the spreadsheet format.

[/code]
 
Upvote 0
Hi again pfhmoney,

I surmised that the data are in columns, but I'm not sure whether all the spaces represent column breaks. For example, with "date 8/25" is date in column A and 8/25 in column B? Or is the entire string in column A? Likewise with "AL-PT01 167"--is AL-PT01 in A and 167 in B? Or is the entire string in column A?

Also, I'm assuming that you can have a lot more columns of data, 1 or 2 for each additional date you get behind depending on whether the spaces are column breaks or not. Is this correct?

Damon
 
Upvote 0
Hi again pfhmoney,

Give this macro a try. I checked it out with a very limited set of data so let me know how it works for you. It should work with any number of dates and any number of part rows per date. I assumed that row 1 is headings and row 2 is empty as in your posted examples

When you run it, the sheet containing the raw data for multiple dates should be the active worksheet. It will create a new sheet and insert it right after that sheet.

Code:
Sub MultipleDayInfo()

   Dim FromSheet  As Worksheet
   Dim ToSheet    As Worksheet
   Dim iRow       As Long     'Row number on FromSheet
   Dim nRows      As Long
   Dim nDates     As Integer  'Number of date column pairs in FromSheet
   Dim iDate      As Integer  'Date column pair index
   
   Set FromSheet = ActiveSheet
   Set ToSheet = Worksheets.Add(after:=FromSheet)
   
   nDates = FromSheet.Cells(1, 256).End(xlToLeft).Column / 2
   nRows = FromSheet.[A65536].End(xlUp).Row
   
   With FromSheet
      For iDate = 1 To nDates
         .Range(.Cells(3, iDate * 2 - 1), .Cells(nRows, iDate * 2)).Copy ToSheet.Cells(3 + (iDate - 1) * (nRows - 2), "A")
         ToSheet.Range(ToSheet.Cells(3 + (iDate - 1) * (nRows - 2), "C"), ToSheet.Cells(2 + iDate * (nRows - 2), "C")) = Day(FromSheet.Cells(1, iDate * 2))
      Next iDate
   End With
   
   'Sort by part
   With ToSheet
      .Range("A3", .Range("C65536").End(xlUp)).Sort Key1:=.Range("A3"), Header:=xlNo

   'Now combine multiple dates
   
      iRow = 3
      
      Do Until IsEmpty(.Cells(iRow, "A"))
         If .Cells(iRow + 1, "A") = .Cells(iRow, "A") Then
            If .Cells(iRow + 1, "B") = .Cells(iRow, "B") Then
               'eliminate row
               .Cells(iRow, "C") = .Cells(iRow, "C") & "," & .Cells(iRow + 1, "C")
               .Rows(iRow + 1).Delete
               GoTo DoAgain
            End If
         End If
         iRow = iRow + 1
DoAgain:
      Loop
   
      .Rows(1).ClearContents
      .Rows(1).Font.Bold = True
      .[A1] = "Location"
      .[B1] = "Code"
      .[C1] = "Dates"
      .Columns("A:C").HorizontalAlignment = xlCenter
   End With
   
End Sub

Keep Excelling.

Damon
 
Upvote 0
Tested macro

Thank you again for all your efforts. I did run the macro however it returned no results other than creating a new sheet with the header of Location Code Date (it did not pull/sort the information).
 
Upvote 0
Found part of the error

The error was mine. I had added a row to the the active sheet, once I eliminated that row it ran wonderfully. It added dates without location and codes... but that was those empty cells found on certain days which did not have as many sites as others... so it put in the day to cover the empty cell. That I can handle! Thank you again. I truly wish I knew enough about Excel and Access but I just do not. If I knew as much as you folks here at MrExcel I could speed up the process immencly at work. I could tap into your brain for everything that I have a need for but that would not be fair to you.

I put the results of your hard labor onto the same web page link as before:
http://www.pennysfromheaveninc.com/speedup.htm if you would care to see your work!
 
Upvote 0

Forum statistics

Threads
1,214,920
Messages
6,122,262
Members
449,075
Latest member
staticfluids

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