UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Seperating Data, Office 2013    
 
   
ollyhutsy
post Feb 4 2020, 03:23 AM
Post#1



Posts: 95
Joined: 16-October 19
From: United Kingdom


At the moment i am using some software called crystal reports to run a report on booked holidays from my clock in system at work. This then exports the data to excel. My problem is that it is exporting the data in 1 column. eg.

Andy Hinkley
24/12/2019 00:00:00
24/05/2019 00:00:00
23/05/2019 00:00:00
Bruce Burton
31/12/2019 00:00:00
26/12/2019 00:00:00
Charlie Tuffin
09/07/2019 00:00:00
10/07/2019 00:00:00
25/10/2019 00:00:00
Cole Wigglesworth
26/08/2019 00:00:00
25/12/2019 00:00:00
Craig Taylor
18/07/2019 00:00:00
26/12/2019 00:00:00
29/11/2019 00:00:00

I would like to create a macro which would sort the data into seperate columns with each persons name at the top. Has anybody got any suggestions??

Go to the top of the page
 
June7
post Feb 4 2020, 01:49 PM
Post#2



Posts: 1,389
Joined: 25-January 16
From: The Great Land


What is this 'clock-in system' - what app? How does CR interact with it? What is code that does the export to Excel?

You want names across top with date/time below each name? That would not be a normalized data structure.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
samgardner
post Apr 7 2020, 04:35 PM
Post#3



Posts: 8
Joined: 6-April 20



Well, you have a number of options, but the simplest I see is:

Have the macro :

  1. create a new sheet
  2. Start a counter at 0
  3. Start a second counter at 1
  4. cycle through each row and scan it for "/" characters
  5. If you do not find a "/", increment the counter by 1 and set second counter back to 1
  6. copy it to the new sheet at Cells(second counter, counter)
  7. increment second counter
  8. go to the next row


If you need to, you can then delete the original sheet/rename the sheets/export/whatever.
Go to the top of the page
 
ADezii
post Apr 7 2020, 05:03 PM
Post#4



Posts: 2,988
Joined: 4-February 07
From: USA, Florida, Delray Beach


Is the below the end result you are looking for?
Attached File(s)
Attached File  Excel4.JPG ( 34.99K )Number of downloads: 5
 
Go to the top of the page
 
FrankRuperto
post Apr 7 2020, 05:10 PM
Post#5



Posts: 967
Joined: 21-September 14
From: Tampa, Florida USA


Hi ADezii,

Another reformatting of an Excel sheet, except this one's not as complicated as the color-coded conditional formatting challenge. Is there more advantage in putting the names and times in rows instead of columns? e.g. for importing into a db?
This post has been edited by FrankRuperto: Apr 7 2020, 05:13 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
ADezii
post Apr 7 2020, 06:03 PM
Post#6



Posts: 2,988
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Is there more advantage in putting the names and times in rows instead of columns?

Hello Frank, I would think that what you have suggested would be more advantageous but, as I see it, it all boils down to what the OP's specific needs and restrictions are.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 12:51 AM