Macro for data present in multiple rows and columns following multiple conditions

Combination is complex

Questions:
For type A, specifically in D4, E5, F6 the data if it exists will only ever be three entries max and will always be staggered like this?

In K14 and L15 you have extra data and I understand you want that data included, if there is extra data like this, will there always be a blank row between?

This data will always be staggered also?

What's going on with columns G,H,I & J?

Hi MacroLearnErkk
.
. I am learning myself and was attempting to answer this as skywriter came in and sensibly tried to clarify what you want. I had started so i finished, but with hindsight maybe it would have been better to wait – I made a few assumptions ( never good to assume in any business , I think )

. For example I……
. Assume Date column 1 is an indication of last entry in any row.
. Other assumptions were based on assuming you picked your test data carefully to reflect all scenarios.. so for example the point skywriter asked about... that “ This data will always be staggered „ is true..


. There are a lot of unnecessary / extra bits in the code because:

. 1 ) I do that, as I am learning! – Helps me remember what is going on. ( And I hears “This is a teaching forum“ ( And debugging is easier !! )
. - Yous can easily work through chopping bits out, simplifying steps ( often by substituting in for a variable what that variable is Let or Set to , so doing away actually using a variable for it etc.. etc… ).
. - And you can think of doing a Function or two, for example to do the adding of an entry in the output Array to save repeating similar code lines.

. 2 ) I left it a bit “raw” , typos etc.. as it is not too clear exactly of the output or possible input data. So I have not checked extensively. It seems to match your hand filled in results exactly

.. here just a small bit of your Test data:

Using Excel 2007

Row\Col	A	B	C	D	E	F	K	L	M
1	Date	Record No	Type	colA1	colA3	colA2	colB1	colB3	colB2
9	06.09.2015		TypeA
10	06.09.2015			gh			gh
11	06.09.2015				kl			kl
12	06.09.2015								ij
13	06.09.2015
14	06.09.2015						mn
15	06.09.2015							qr
16	06.09.2015
17	06.09.2015	1616
18	06.09.2015		TypeA
19	06.09.2015			uv			ef
20	06.09.2015				yz
21	06.09.2015								gh
22	06.09.2015
23	06.09.2015						kl
24	06.09.2015							op
25	06.09.2015
26	06.09.2015	6537
27	06.09.2015		TypeB
28	06.09.2015				12
29	06.09.2015							34
30	06.09.2015
31	06.09.2015							56
32	06.09.2015							78
33	06.09.2015	2356
34	06.09.2015		TypeB

Sheet1

.. and then the output my code gives ( for the full data you gave )

Using Excel 2007

Row\Col	A
1	ab cd ef
2	mn op qr
3	gh kl
4	gh ij kl
5	mn qr
6	uv yz
7	ef gh
8	kl op
9	12
10	34
11	56
12	78
13	ab
14	cd
15	ef
16	ab
17	cd
18	ef
19	12
20	34
21	56

Output

Let us know how you get in please and answer skywriter’s questions. Maybe we can give then another / better or improved alternative. I used Arrays just ‘cos I’m in to that recently.. Most would do it with spreadsheet stuff, I think..

Alan

Code in next post, post #4

Code for last Post # 3

Please Login or Register  to view this content.

Combination in detail

I'm not sure you really answered my concern regarding the data for record 1456. Okay I understand the D, E, F part. My concern is the multiple data in K, L, M. You have three data entries. gh, kl, ij and then a gap of a row in between, I asked you a question regarding this gap will it always be there if there is multiple data. Notice how row 13 has no data.

Questions
#1 Will there be a blank row between multiple data sets as in the example I'm citing.

#2 If there are multiple data sets will it always be set of threes before the last data set. In other words here we have a set of three and then a set of 2, so if it was even more data can we count on it to be let's say in an example of 4 sets of data 3 sets of 3 and then the last set which might only be two?

Combination not clear

I'm going to pass. Too much trouble at least for me the way it's formatted. Staggered data, out of order concatenation. You gave an example with a blank row and now say it wouldn't have a blank row. Sounds like there are probably some other variables that are going to cause issues. Maybe someone else will post something that will make it look easy, but for me I don't exactly know how to approach it. Maybe Doc A. can help you.

Seems that macro I proposed in previous thread can be updated to take into account these additional data:

Please Login or Register  to view this content.

As you can see main change is different record type recognition mechanism:

Please Login or Register  to view this content.

And in

Please Login or Register  to view this content.

second loop is repeated in 3-rows chunks (I marked blue external loop - what is inside is the old code (only difference with original - just limited to 3 rows):

Please Login or Register  to view this content.

while

Please Login or Register  to view this content.

is left untouched

Enjoy!

@Kaper,

Thank you.

Sorry to say so, but probably your data has different layout than the sample you posted. See code inserted in your sample file. It works!

@ skywriter

Originally Posted by skywriter

I'm going to pass. Too much trouble at least for me the way it's formatted. Staggered data, out of order concatenation......... Maybe Doc A. can help you.

.. I think ( maybe by luck or design – I cannot remember now ) I got my code to work on what I still think is his requirement, and I think his further replies to you have confirmed that. In any case it certainly gives still the results exactly as he wanted. ( I just got a bit confused now. / recently whether he wants to get any diagonal going right downward in the order K L M or as originally stated K M L. But again I think I have it right in the original code. And I think I understand his further answers to you. I think you may have been looking for ( I just thought a possible consequence might be if there is a an empty cell in L , but not in K and M in a diagonally going down to the left that he wants my code would give an extra space between the two concatenated values, but an extra Replace(___ bit could be added to change any two spaces to one so no problem there )

Alan
………………………………………………………..

@ Kaper

Originally Posted by Kaper

Seems that macro I proposed in previous thread can be updated to take into account these additional data:........

. Thanks for supplying an alternative. I learn the most when I see how a Pro does a version of a solution I give. I will try to find the time to do justice to that later and work through it thoroughly and learn from it. At first glance I think I may be doing something very similar to you, but just in Arrays. And you do it more professionally and tidily by calling your Action Subs

( I missed the OP’s earlier Thread, it is almost a Duplicate, or rather would have been good to reference it when he started this Thread…. Thanks for catching the connection there.. )
………………………………………………

@ Learningerrk
. I think from all your further replies it appears my original code ( Post # 4) is doing what you want. I have tried Kaper’s code from the File given in post # 9 and it produces the same results as mine, that is to say exactly the hand filled in Output Sheet you gave originally.
. Kaper’s code is a typical “spreadsheet interaction type” code so makes a mine comparison to mine.
. Please let us know how you get on with both codes.
. Let me know if I should simplify my code a bit, if you have difficulty doing that.
.
Alan

………………..
P.s. 1. )
For the next time, when staring such a Thread which clearly follows on from another, it would be helpful to reference it with a link like
http://www.excelforum.com/excel-prog...rent-rows.html
.. as that may help someone get started.

P.s. 2 )
I just thought a possible consequence might be if there is a an empty cell in L , but not in K and M in a diagonally going down to the left that he wants my code would give an extra space between the two concatenated values, but an extra Replace(___ bit could be added to change any two spaces to one so no problem there . But maybe some of you answers to skywriter suggested that situation may not occur anyway.

Originally Posted by Kaper

Sorry to say so, but probably your data has different layout than the sample you posted. See code inserted in your sample file. It works!

... Yes it does
( gives same results as mine ( from Post # 4 ) and identical to what the OP gave as sample hand fiiled - in wanted Output

Originally Posted by macrolearnerkk

@Kaper, @skywriter, Thank you once again for your valuable contribution. I really appreciate it.

.. Hi,
. Any Feedback on my code ?

Alan