+ Reply to Thread
Results 1 to 6 of 6

Convert format to excel

  1. #1
    Registered User
    Join Date
    07-14-2012
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    4

    Convert format to excel

    I have a member directory that is formated one way and I need a convient way to convert it into a usable spreadsheet. Is this possible? Can someone suggest an easy way to do it? I have attached a small sheet that illustrates our problem and attached a picture of the info source. Thanks in advance.


    Membership level
    Associate
    First Name
    Scott
    Last Name
    Baumbach
    Office Phone
    (817) 431-1541
    eMail
    [email protected]
    Organization
    Tri-county Electric Cooperative Inc
    Title
    (none)
    Website
    www.tcectexas.com
    Address
    4740 Keller Hicks Rd
    City
    Keller
    State
    TX
    ZIP
    76244
    Fax
    (817) 421-9680
    Associate Classification
    Utillity Company
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Convert format to excel

    Hi
    I attached the file and filled the columns B, C and D, with auxiliary formulas.
    In column F I put the desired information.
    The column G are the results for the first group (the group must have the [Membership level] field) and can be extended to the following columns.
    Regards
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Convert format to excel

    Here a VBA solution for you.
    It uses the activesheet so you can put a button on any sheet, assign it to the macro & it'll work for that sheet

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-14-2012
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Convert format to excel

    Jose, Thank you. I want to learn how to do it myself. Can you walk me through how it was done?

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Convert format to excel

    All right. I'll try to show you how the formulas work and why to use.

    1. You decided the fields you want to use and put them in [F2:F15]. You could put them in any other part of another sheet.

    2. Data (column [A:]) can now be cataloged with a label that allows us to recognize that it is one of the fields to be used. 1 for Label field in the first position, 2 for the second and so on. The formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    which means that if there is a field in cell A2 then look for this field in the table [F2:F15] with exact demand. The column [B:B] now has this information.

    3. To organize the groups I chose the field [Membership level] <=> [label 1]. Thus, only the groups that have [Membership level] <> "" will be considered. From [B$2] (the begin) I check the frequency of the label 1 and appearance of this sort so the fields in each group with the same tag.
    The formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    where 1 is the [Membership level] field position.

    4. I need to build a unique key to access each of the fields of a given group. This key is formed by the group number and the name of the field. The formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    .
    Now the fields are perfectly identified in the data set to [$A$2:$D$628], and its value is what is stated in column A of the line that follows the line of the field.

    5 We must now explain the formula =IFERROR(INDEX($A$2:$A$628,MATCH(COLUMN(A:A)&$F2,$D$2:$D$628,0) +1,1),"(none)")
    COLUMN (A: A) is 1 and is the group I want to fill. If copied to the next column gives 2 for group 2 and so on. Concatenating this value with the name of the COLUMN field (A:A)&$F2 to get [key] to access to information
    Thus, simplifying the formula
    IFERROR=(...,"(none)") -> If field exist then ... else "(none)"
    we get
    = INDEX($A$2:$A$628,MATCH(COLUMN(A:A)&$F2,$D$2:$D$628,0)+1,1)
    and simplifying the writing will
    = INDEX(Data,MATCH (key,data,0)+1,1)
    Where +1 Is the next row to access the value of the group field identified with Key and 0 is exact match.
    Eu anexei um ficheiro com a informação organizada por lista.
    Best regards
    Attached Files Attached Files

  6. #6
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Convert format to excel

    All right. I'll try to show you how the formulas work and why to use.

    1. You decided the fields you want to use and put them in [F2:F15]. You could put them in any other part of another sheet.

    2. Data (column [A:]) can now be cataloged with a label that allows us to recognize that it is one of the fields to be used. 1 for Label field in the first position, 2 for the second and so on. The formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    which means that if there is a field in cell A2 then look for this field in the table [F2:F15] with exact demand. The column [B:B] now has this information.

    3. To organize the groups I chose the field [Membership level] <=> [label 1]. Thus, only the groups that have [Membership level] <> "" will be considered. From [B$2] (the begin) I check the frequency of the label 1 and appearance of this sort so the fields in each group with the same tag.
    The formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    where 1 is the [Membership level] field position.

    4. I need to build a unique key to access each of the fields of a given group. This key is formed by the group number and the name of the field. The formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    .
    Now the fields are perfectly identified in the data set to [$A$2:$D$628], and its value is what is stated in column A of the line that follows the line of the field.

    5 We must now explain the formula =IFERROR(INDEX($A$2:$A$628,MATCH(COLUMN(A:A)&$F2,$D$2:$D$628,0)+1,1),"(none)")
    COLUMN (A: A) is 1 and is the group I want to fill. If copied to the next column gives 2 for group 2 and so on. Concatenating this value with the name of the COLUMN field (A:A)&$F2 to get [key] to access to information
    Thus, simplifying the formula
    IFERROR=(...,"(none)") -> If field exist then ... else "(none)"
    we get
    = INDEX($A$2:$A$628,MATCH(COLUMN(A:A)&$F2,$D$2:$D$628,0)+1,1)
    and simplifying the writing will
    = INDEX(Data,MATCH (key,data,0)+1,1)
    Where +1 Is the next row to access the value of the group field identified with Key and 0 is exact match.
    Eu anexei um ficheiro com a informação organizada por lista.
    Best regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel: How to convert time format into number format that can be added?
    By Frances Jones in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2012, 04:10 PM
  2. Replies: 4
    Last Post: 06-03-2011, 01:48 PM
  3. Excel date format convert to string format
    By man in forum Excel General
    Replies: 1
    Last Post: 08-17-2006, 05:05 AM
  4. how do i convert excel to csv format
    By baxterr in forum Excel General
    Replies: 1
    Last Post: 11-02-2005, 07:35 PM
  5. Replies: 2
    Last Post: 07-06-2005, 02:05 PM

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