Roberto has attended:
Excel Advanced course
Excele
Hello there
I have attended a Excel Advanced course with you 10 days ago
I made this course also because of the following problem
To make myself undeerstood I would need to send you and attachment
This attachment is an excel report exported from google analytics which details the visits to different pages of our website
We rent villas; and each villa ha a description which is split in 5 sections
So each villas have 5 different type of pages (separate urls)
• _CardID
• _PicturesID
• _PriceID
• _DescriptionID
• _CommentsID
I need to group these 5 types in just one; which is the name of the Villa
• This name is “contained” in the column A of first sheets between the text “/Rentals/Villas/” and one of the 5 text above: “_CardID
” etc etc
For example
/Rentals/Villas/La_Trappola_CardID_4663.aspx 1542
/Rentals/Villas/La_Trappola_PicturesID_4663.aspx 1509
/Rentals/Villas/La_Trappola_PicturesID_3840.aspx 1473
I need to group below 3 rows in just one. Named La_Trappola with a total of 4524
I was trying to do this using the VLOOKUP function
But I cant!!
Can you please help
Many thanks
Below my delegate login details
Username: robertolucci@hotmail.com
Password: 6117
RE: excele
Hi Roberto, thanks for your query. What you're trying to achieve is possible but requires a combination of tools. When you have text or values within a string (in your Column A, I think) you are going to have to "parse" the data to lift out the information into separate fields. You can do this quickly with the text to columns tools in Excel. If you want to lift the name out of the strings cited above, you'll need your text formula functions (LEFT, MID, RIGHT etc).
Then when you have the data in separate fields, you can apply your grouping tools, which will also allow you to subtotal the numerical values. Have a go and see how you get on, if you need any resources regarding the tools I have mentioned drop me a line on the forum.
Hope this helps,
Anthony
RE: excele
Hello Anthony
thanks for your help
I was actually thinking about the MID function
but the trouble is that the name of villa to be extracted from a longer text has a variable number of charcaters
In the example above i know that the name of the villa starts a position 17 but i dont know when it ends; as this depends on the lenght of villa name
is there any way around?
thank you
roberto
RE: excele
Hi Roberto.
You need to count the number of letters in the string (=LEN(A1))
You should have a fixed number of letters at the end of the string (e.g. _4663.aspx has 10 characters). Add this to the position of the character to want to start from in the MID formula, this gives you 27 characters you want to lose from the overall string, however long that string is:
Therefore for each cell:
=mid(a1, 17, LEN(A1)-27)
Hope this helps,
Anthony
RE: excele
Hi Anthony
unfortunately the length of the string (eg urls number of charcater) is variable
thats because the name of the villa is variable;
any other idea?
roberto
RE: excele
HI Anthony
I have a more precise rule that i have to follow that may help you
I need to extract from a longer string the part included between
The string “Villas/” (which terminates always at charcater number 16)
AND
The string “_CardID”
OR
The string “_DescriptionID”
OR
The string “_PicturesID”
OR
The string “_CommentsID”
OR
The string “_PriceID”
One of the string above starts at a variable point. And this is the problem
Is there any formula I could use playing with strings above?
Thank you
roberto
