+ Reply to Thread
Results 1 to 9 of 9

Organizing Data

  1. #1
    Registered User
    Join Date
    08-06-2013
    Location
    North Carolina
    MS-Off Ver
    Excel 2007
    Posts
    4

    Organizing Data

    I've been tasked with a project to manipulate some data that already exists in an excel document. I need to search a column for all URLs containing a string that I specify, then pull data from a different column in the same row as the search result to organize in another worksheet. I have attached an example spreadsheet with what I'm given and the desired results manually entered in red. I have a feeling its going to take some nested lookups or matches but I can't quite figure this one out.

    EX: This corresponds to the attached spreadsheet.

    Task: To fill in the table on Sheet2 with the data from Sheet1 as indicated.

    I need to somehow search Sheet1 for a given partition (e.g. p1) appended to the front of the internal URL and place its corresponding external URLs into a table on Sheet2.

    What is written in black is what I have and what is in red is what I would like the formula to produce.

    Thanks for the help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Organizing Data

    Why not create a pivot table?
    Why do you want the URLs in that table format vs a two column table?

  3. #3
    Registered User
    Join Date
    08-06-2013
    Location
    Cheltenham, England
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: Organizing Data

    Here's a pivot table example for your data:
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-06-2013
    Location
    North Carolina
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Organizing Data

    Unfortunately a pivot table will not provide what I need. The task I have is much more complicated than the example I posted. There are well over 200 URLs and around 20 partitions along with miscellaneous data existing in columns in between. My end goal is to make a parent-child flow chart with the data I gather from Excel in Visio. This will show what URLs will go down if a certain partition were to crash. I wrote a formula that worked for what I needed, however it was locating an exact match as opposed to matching just the first part of the URL.

    This is the basic structure of my formula (?'s are places I'm confused):

    =index(?E&H?,small(if(H:H=???,row(H:H)),row(1:1)),?)

    I can't figure out how to index this formula to provide the correct results. I need it to index the row number of the match it finds in column H and return the data from the same row number in column E. This corresponds to the ?'s at the beginning and end of the formula.

    The formula was finding "p1.domain.com" but I want it to find "p1..." where the 3 dots can be any number of characters. This point applies to the H:H=??? part of the formula.

  5. #5
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Organizing Data

    You do realize the purpose of posting a sample data set is so we have an idea of what you are trying to accomplish. So if your sample is not relevant then YOU are the only person who can know that and we are wasting our time even looking at it.

    I suggest you construct a data set that represents the problem, that also includes a potential solution layout of some sort.

  6. #6
    Registered User
    Join Date
    08-06-2013
    Location
    North Carolina
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Organizing Data

    You do realize the sample data is relevant to my task. It is the exact same structure for the formula except the formatting is different. So instead of berating me for trying to keep the example simple, why don't you actually do something constructive like help me with the formula's structure. In the mean time, I'll put together a more thorough example that better represents the formatting of the task.

    Next time, I suggest you answer the question originally asked. That way I can thank you, then add a follow up question about the formatting of the formula to fit what I need.

    Thanks!

  7. #7
    Registered User
    Join Date
    08-06-2013
    Location
    North Carolina
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Organizing Data

    After the formatting...

    (such a huge difference..)
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Organizing Data

    [QUOTE=ncstate;3355527The task I have is much more complicated than the example I posted.[/QUOTE]

    Quote Originally Posted by ncstate View Post
    You do realize the sample data is relevant to my task. It is the exact same structure for the formula except the formatting is different. So instead of berating me for trying to keep the example simple, why don't you actually do something constructive like help me with the formula's structure. In the mean time, I'll put together a more thorough example that better represents the formatting of the task.

    Next time, I suggest you answer the question originally asked. That way I can thank you, then add a follow up question about the formatting of the formula to fit what I need.

    Thanks!
    It seems you missed my point.

    Your posts are not very clear and they seem to indicate that the sample data set is not reflective of the actual problem/solution. See your qoutes above.

    I suggest you always have a clear and concise problem statement with a clear and concise expected outcome for people to come in here and offer free advice. This has nothing to do with EGO, but a statistical view on people's problems and expected outcome from the forum based on those two factors: concise problem/solution, relevant sample data set.

    Next time I would suggset you not assume this has anything to do with your ego, (or anyone's ego for tha matter) and simply try to comprehend the CONSTRUCTIVE comments given to you, as they are designed to help you.

  9. #9
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Organizing Data

    Also, my point was in fact that it is likely that the forum did NOT think the sample data set was relevant (since I did not), which is why I gave you that feedback.

+ 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. Re-organizing data
    By robbo46 in forum Excel General
    Replies: 3
    Last Post: 09-09-2011, 07:58 AM
  2. organizing data
    By Dane in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-03-2008, 06:35 AM
  3. Help with organizing data
    By Eaglered in forum Excel General
    Replies: 2
    Last Post: 05-18-2005, 03:06 PM
  4. Organizing Data
    By trini in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-21-2005, 12:06 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