+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 16

Help with more manageable format

  1. #1
    Registered User
    Join Date
    05-08-2017
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    12

    Help with more manageable format

    Hello Forum,

    I'm a police officer in the US, and I've been working on tracking down people in my jurisdiction that have unfinished business with the court system (people with felony warrants). Today I found a way to receive more encompassing warrant lists. Those lists are in Excel format. The catch is that the format is not friendly for data sorting. I've attached an example if you have the time and are interested in helping me get this data into a more manageable form. My Excel skills are limited, which is why I'm reaching out to this community.

    The bottom line is that I'm looking for all fields for each individual to be in columns, and each row to consist of data for each individual. For example, the first entry of DINGLEBERRY, THOMAS DEAN should have all of DINGLEBERRY's information across the entire row, starting with his name and ending with "Remarks". His address should consist of his address only (deleting the redundant word "Address" in the field) and I'd like to separate the street number into its own column apart from the street name so I can sort by street name at a later time.

    These lists can be small like the example I have attached, or they can get up to hundreds of entries long. Once I get the appropriate steps to change the formatting, I plan on creating a macro to accomplish the task automatically (I at least know how to pull that off on my own).

    Let me know what you think and thank you in advance for your time.
    Attached Files Attached Files

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    IXL,BXL,BE,EU,Earth
    MS-Off Ver
    PeeSee(64)=ROUND(4.831^4.831,0)
    Posts
    3,615

    Re: Help with more manageable format

    Atomize your "table" to sepearate columns, ie. SysID, Family name, Name1, name2, s e x, race, district, city, street, st.number.... etc
    will be much easier for future

    edit: don't use merged cells and bells&whistles like weird formats
    Last edited by sandy666; 08-12-2017 at 02:10 AM.
    sandy (NO CAPS)
    Excel can do everything but still doesn't read user's wishful thinking
    The ready made solution will not teach anyone anything

    A logical description of the problem is the basis of dialogue and a rapid solution. Nobody sits in your head to understand your way of thinking
    Note: English is not my native language
    Keep It Sophisticatedly Simple

  3. #3
    Registered User
    Join Date
    05-08-2017
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with more manageable format

    Thanks for your fast response. I agree that some of the data should be atomized... but as you can see, the data in each field is going to vary based on what is entered. For example, the name may be John Doe, or John Douglas Doe, or John Douglas Doe III, or John Douglas Doe Jr... Same with streets, there's no telling how many different words a street name may possess (North Sandy Tree Lane), which is why I want to pull just the numbers from the street names, but I'd like to keep the street names intact so they will fall under a single column for "Street Name". I'm comfortable with the subject's name all being in one field as well. I don't have a use for separating by first name, and the data will always come with the last name first which I may want to sort by.

    The attached spreadsheet is an example of the data as I receive it. I did not make this spreadsheet or enter the data myself. I am, however, attempting to change the formatting to use for sorting and uploading to mapping software which requires the data for each subject to be in a single column.
    Last edited by Reason282; 08-12-2017 at 02:18 AM.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    IXL,BXL,BE,EU,Earth
    MS-Off Ver
    PeeSee(64)=ROUND(4.831^4.831,0)
    Posts
    3,615

    Re: Help with more manageable format

    1 column: Doe
    2 column:John
    3 column: Douglas
    4 column: Jr
    5 column III
    etc. with the street the same
    it will be your basic table.
    if you do this, you will get as many info as you want with pivot table or power query (add-ins to excel 2010)

    of course appropriate title for each column

    edit:
    if you think table will be too big you can divide table to two or three with relationship between them, means common column (I assume it could be SysID) with unique value for each person (each record - in this case: each row)

    a little simple example file attached
    Attached Files Attached Files
    Last edited by sandy666; 08-12-2017 at 05:05 AM.

  5. #5
    Registered User
    Join Date
    05-08-2017
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with more manageable format

    Hey Sandy,

    Thanks for your response. I appreciate your confidence in my Excel abilities... but I'm not on par with the majority of people in this forum. I didn't create the data in my attachment and I feel like your advice is geared toward the people who generated that spreadsheet. However, it was generated by someone else and sent to me. I will receive updated reports similar to the one I attached on a weekly basis and in mass quantity. I'm looking for some guided assistance in changing the formatting of the data once I receive it. I need to know how to get the rows of "Address" through "Remarks" into the same row as the individual's name.

    I have a feeling what I'm asking for is so simple to you that you're assuming I already know how to do it...

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    IXL,BXL,BE,EU,Earth
    MS-Off Ver
    PeeSee(64)=ROUND(4.831^4.831,0)
    Posts
    3,615

    Re: Help with more manageable format

    I thought it was just a one-off example and was used to create your own database but I see that it would be copy / paste by formula (if any will do that)
    I'll look at this later but maybe someone else will be faster

    btw. could you ask for source not result file(s) ?
    Last edited by sandy666; 08-12-2017 at 07:04 PM.

  7. #7
    Registered User
    Join Date
    05-08-2017
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with more manageable format

    Hey Sandy, I don't have access to the source files.

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    IXL,BXL,BE,EU,Earth
    MS-Off Ver
    PeeSee(64)=ROUND(4.831^4.831,0)
    Posts
    3,615

    Re: Help with more manageable format

    Quote Originally Posted by Reason282 View Post
    I don't have access to the source files.
    Pity

    Ok, I need one or two more results tables (like the first) to check if my idea works

    and I'd like to know how you want to divide address and remarkas with the corresponding column names (from the left to the right)
    see att.
    Attached Files Attached Files
    Last edited by sandy666; 08-12-2017 at 10:55 PM.

  9. #9
    Registered User
    Join Date
    05-08-2017
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with more manageable format

    Attached is an additional example file. Thank you for taking the time to help.
    Attached Files Attached Files

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    IXL,BXL,BE,EU,Earth
    MS-Off Ver
    PeeSee(64)=ROUND(4.831^4.831,0)
    Posts
    3,615

    Re: Help with more manageable format

    see attached file from post #8

    but I think Remarks need not be divided
    Last edited by sandy666; 08-12-2017 at 11:05 PM.

  11. #11
    Registered User
    Join Date
    05-08-2017
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with more manageable format

    That's what I'm looking for with the additional aspect of separating the street number from the street name, and, of course, all of the data would be in a row from subject name through remarks.

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    IXL,BXL,BE,EU,Earth
    MS-Off Ver
    PeeSee(64)=ROUND(4.831^4.831,0)
    Posts
    3,615

    Re: Help with more manageable format

    My dear friend
    I'd like to see HOW you want divide ADDRESS,

    eg. 23814 SJJFKKDJS APT 933
    23814 | SJJFKKDJS APT 933
    or
    23814 | SJJFKKDJS | APT | 933

    there are letters also like NESW, so I suppse this is Nord, East, South and West
    or
    enough number and the rest in two columns?

  13. #13
    Registered User
    Join Date
    05-08-2017
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with more manageable format

    Thanks again for your time, Sandy. I'd like the address divided such:

    23814 | SJJFKKDJS APT 933

    The entire address AFTER the numbers can be in one cell. THANK YOU!

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    IXL,BXL,BE,EU,Earth
    MS-Off Ver
    PeeSee(64)=ROUND(4.831^4.831,0)
    Posts
    3,615

    Re: Help with more manageable format

    Ok,
    1. if you want more data from source (from your Dept.) copy data without first and last row and paste into src sheet below previous data
    2. on ind sheet you've result (to row 401), if you want more select A400:Q400 and drag down as far as you need
    3. but remember, too much data will slow down your excel
    4. sorry if it is not click&dance

    maybe someone else will give you much better solution

    see att.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    05-08-2017
    Location
    Detroit, Michigan
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with more manageable format

    Sandy, thank you very much for your time and effort. The data you produced looks exactly like what I'm looking for. This will definitely be a big help to our department. Thank you.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1