Splitting the names of 3 records and displaying only two of them

[shanck]

Hi All,

I understand that title I have put is not clear. Please bear with that as I am unable to put it in a line.

My problem is , in the file I have attached there are records with 3 cars in a single RECORD as Creta-Duster-Jazz ,there are records with 2 car names in a single record. So my requirement would be to get all combinations of cars, only 2 of them to be displayed. For say, there is Creta-Duster-Jazz in one and Creta-Duster in other. I want the records to split in such a way that Creta-Duster should have 2 records.

Please let me know in case of any queries.

[Doc.AElstein]

Hi

...... in the file I have attached there are records with 3 cars in a single RECORD as Creta-Duster-Jazz ,there are records with 2 car names in a single record. So my requirement would be to get all combinations of cars, only 2 of them to be displayed. For say, there is Creta-Duster-Jazz in one and Creta-Duster in other. I want the records to split in such a way that Creta-Duster should have 2 records.
...

. You have explained quite well what you want, and I think i get the general idea.
. But It would be much easier if you give a clear picture ( Not IMAGES ) of what you have before and after with reduced test data.


. So simply provide clear before and after ... ( For example a "Before" and "After" sheet in an Excel File )

To summarise.

. The Before should look just as it does before running of any macro.
.
. The After should be hand filled by you so that it looks exactly as you want it to After running of any macro, based on the actual sample data in the Before
. Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary, but use the typical Format type and choose your data carefully so that it can be used to test all possible scenarios.
...

Alan

[shanck]

Thanks a lot for the reply. Please find the updated sheet with some points. Hope it would make you clear.

[Doc.AElstein]

..... Please find the updated sheet with some points. Hope it would make you clear.

. Ok I think that makes it clearer.
. I shall take a look later Today or Tonight if no one else picks up the Thread in the meantime.
. I think it is possible, but may take a while to figure out how!!

Alan

P.s. Do you have a listing of all cars

/tata-bolt
/honda-jazz
/skoda-octavia
......
.....

........

etc.
etc.

. - The code could produce such a list from your "Sheet1" list in column A, but it would be easier if you have such a list already.

[shanck]

Sorry sir. I just have that list I sent.I dont have the list of all those

[Doc.AElstein]

Sorry sir. I just have that list I sent.I dont have the list of all those

. Ok, Thanks for letting me know.
. It makes the code a bit more difficult but I think i can still do it.
. I will post back If / When I get a solution working

Alan

[Doc.AElstein]

Hi shancky,
. I have a code which based on your descriptions and test data seems to work
.
. I expect with final Full Test Data it may not fully meet your requirements. I expect your descriptions and in particular your test data was not carefully enough selected to demonstrate and test all possible scenarios.
.
. I leave you to test fully the code. Then if you are able make the appropriate modifications or reconsider carefully your description and test data selection. Re submit that then to me and I will modify the code appropriately. ( I expect, for example, there could be unwanted Duplicated rows when using full test data. )

. In any case, please reply back with comments and share your findings, or if you are able, please present the modified code you do based on this code which I give.
.............

To Summarise.

My code will take your supplied test data.. ( Your Before sheet )

Using Excel 2007

Row\Col	A	B	C	D	E	F
1	Page path level 2	Pageviews	Unique Pageviews	Avg. Time on Page	Bounce Rate	% Exit
2	/hyundai-creta-and-renault-duster.htm	23166	18477	175.8	32.20%	59.81%
3	/hyundai-i20-and-maruti-swift.htm	21341	13990	118.1	45.07%	30.93%
4	/hyundai-creta-and-hyundai-creta.htm	15253	10255	191.8	22.91%	29.15%
5	/honda-amaze-and-maruti-swift-dzire.htm	13217	8762	126.8	46.77%	26.54%
6	/hyundai-creta-and-maruti-sx4-s-cross.htm	11257	7370	188.7	25.55%	29.42%
7	/honda-city-and-maruti-ciaz.htm	11212	7498	123.9	39.32%	31.97%
8	/honda-city-and-hyundai-verna.htm	10662	7209	122.9	43.51%	33.75%
9	/honda-amaze-and-hyundai-xcent-and-maruti-swift-dzire.htm	9528	4930	158.1	33.43%	34.03%

Sheet1

. .........
. Working with the above test data my code produces a new sheet named “Sheet2” if that sheet does not already exist.
. Then it produces in that sheet the following results, which are identical to those you presented in your After sheet

Using Excel 2007

Row\Col	A	B	C	D	E	F
1
2	/honda-amaze-and-maruti-swift-dzire.htm	13217	8762	126.78	46.77%	26.54%
3	/honda-amaze-and-maruti-swift-dzire.htm	9528	4930	158.08	33.43%	34.03%
4

Sheet2

............................

. speak to you again later when you have had time to try the code and replied back

Alan

. Code (Sub A_WankyShanky1() ) I provide in the next Post, Post # 7

[Doc.AElstein]

. Code (Sub A_WankyShanky1() ) for last Post # 8

Please Login or Register  to view this content.