general thoughts on getting text from columns

[christhedonstar]

Here is an example:

Say you have a config worksheet and you have loads of data in this table related to people. I now want to add the cars people own and then later extract the cars they own without using vba.

The two ideas I have is taking (note | is next cell):

Name | Car 1 | Car 2 |
Chris | Peugout 205 | Audi A3 |
John | Audi A3 | Golf | Mini

I can just do a vlookup and match on Car 1, Car 2 etc.

Here I can do a vlookup and then match on Car 1, Car etc.

OR

Chris | Peugout 205, Audi A3
John | Audi A3, Golf, Mini

Here I can do a vlookup and then string functions to get the cars using the comma as a delimiter

I'm interested in speed and maintenance. Obviously we don't know how many cars people have but lets say 6 is likely over the top but possible. I'm currently leaning towards doing the comma seperated as you don't need to add an extra column to expand the amount of cars someone can have.

Thoughts?

Thanks,

Chris

 

[SkipVought]

Hi,

Entirely wrong approch.
[tt]
Name | Car
Chris | Peugout 205
Chris | Audi A3
John | Audi A3
John | Golf
John | Mini
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[christhedonstar]

The problem is there is a lot more information that is related to each person lets say another 20 attributes. We'd be duplicating all that info. Hence why I had only the two options to start with. Of course there could be another option.

 

[christhedonstar]

Having said that how do you find the 3rd occurence of a string in a string?

e.g. "Peugot, Audi, Mercedes"

How do I find the position of the 2nd , without vba?

 

[SkipVought]

" how do you find the 3rd occurence of a string in a string"

That can be done, but that is NOT the way that I would recommend storing your data.

So you have another 20 attributes.

One attribute is CAR. (not Peugot, Audi, Mercedes)

What are the other attributes. This is not rocket science; it's standard database design and analysis techniques.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[christhedonstar]

Limited to keeping the data on a spreadsheet (its the rule of the project unforunately - I might have done everything in vba or c# otherwise). All the others attributes are unique and not related to each other. This is these are the only attributes where you have a one to many mapping. I could have another table, how you specified but I don't own maintaining it and I think the users won't like it.

The formula I have so far seems to be getting complicated...

=LEFT(C$3,FIND(CHAR(1),SUBSTITUTE(C$3,",",CHAR(1),B6))-1)

Looks like I'm going for the vlookup match on car1 car2 thing

 

[SkipVought]

"Limited to keeping the data on a spreadsheet "

???????????

I was not suggesting Mars!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Listen to Skip. Seriously.

Excel isn't designed for the type of setup you're talking about, and you'll only make your life - actually, the life of the person who does maintain the sheet - much harder if you design it poorly. I've made this mistake in the past and learned the hard way.

If you set up your table correctly in the first place, you will not only make reporting on and summarizing data easier, but you'll also never have to worry about how many cars someone owns.

If you give us an idea of what kind of data is being stored and the purpose for your creating this spreadsheet, we can suggest better design options.

- -
The timing of this thread is funny.... Just yesterday I had to help someone get data from the type of setup you're looking at into a normalized table so he could report on it how he wanted.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[christhedonstar]

I meant as opposed to a file or database. Really I should have said I'm limited to doing everything in Excel.

 

[SkipVought]

I am not suggesting ANYTHING different.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Can you point out where I suggested doing anything NOT in Excel?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[christhedonstar]

Ok found a way of looking at it. I'll just stick to the table where by instead of having car 1 and car 2. I have a family car, sports car and a three wheel car in a made up world where people can only have one of each of the three type of cars.



Thanks,

Chris

 

[SkipVought]

ctds,

What a POOR crutch! Not every family has 3 cars. Some families have MORE THAN THREE. I happen to have a truck. Where do you fit that? Your data structure is faulty at best.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

christhedonstar - listen to these guys, they know what they are talking about. You don't HAVE to fallow their suggestions, but I am sure you WILL learn a lot.

Remember, in your little app you may come across Jay Leno or Jerry Seinfeld (if you are in US) and they have MANY, MANY cars.


Have fun.

---- Andy

 

[lionelhill]

Chris, I'm not sure you realise what you're throwing away if you choose not to follow Skip's advice.

For instance, if you do things his way, you can mark the entire block of data, and use the "Pivot table wizard" to convert it to a pivot table, and then with a single mouse click (actually a double-click!) you'll be able to extract your data into new tables showing all the cars owned by particular people, or all the people who own a particular sort of car, or all sorts of other things. You can extract summary data such as numbers of cars owned, numbers of people with particular cars, etc. etc.; the more extra data you are having to keep, the more valid this approach becomes, because you can search it in more, and more interesting ways.

Unfortunately your project is to create a database using a spreadsheet program, which isn't ideal, but if you use Excel correctly, it can help you.

There are two things that might be worrying you about adding extra lines of data where "John" exists 3 times because he has 3 cars. Firstly you might be worrying about the wasted memory of storing the same information 3 times. Excel can handle some pretty big files, so this is unlikely to be a problem. Secondly, you might be worried about extra typing, and errors where John's second car ends up with different data to his first. This is also soluble using standard database techniques. For instance, if you want to keep John's age, this is clearly a feature of John, and not his car, so you could keep a second table of "people" with personal data, and if you need it in a grand-table with car information too, do a look-up to extract the data from the 2nd table. Similarly, if you have information about Audis, it could be kept in a table of "cars" and reproduced next to John's data where he owns an Audi.