Appendix Thread. 3 TEST COPY

Here is an alternative single liner ( almost ## ) type solution to the last post. It was much simpler than I expected, and ends up much shorter than these solutions of mine usually do. (## There was a small snag, not solved yet, which means I have to do it in 2 code lines for now. I may take a look at that later here: https://excelfox.com/forum/showthrea...ll=1#post16655 )

Solution explanation.
Part 1. Background
This is all to do with
_ “my”** ____arrOut()=Index(ArrIn(), Rws(), Clms()) ______ type solutions, ( https://www.excelforum.com/excel-new...ml#post4571172 )
and also
_ using the Match in a similar way – ( some time ago I obsessed with trying out Application.Match where the first argument is an array, in a similar way to those of those array arguments Rws() and Clms() in Index. I got so obsessed I littered a sub forum with over long posts until they deleted them all and limited the post size to stop me doing it again. With hindsight, not a bad thing to do, as I could not see the wood for the trees back then. I can now, and its not at all difficult to understand, so I really don’t need all that crap anymore. Let me call that for now “my” **
________arrOut() = Match(arrArg1(), arrIn() , 0 ) ___ type solution.
( ** I use the word “my” lightly. – I learnt all this stuff from looking at stuff from Rick Rothstein and snb. ( I am not sure if they “invented it” , or got it from other peoples stuff. if I added anything “new” , it might be some of my detailed explanations, which whilst I don’t know if they are correct, they seem to be a valid theory as they go a long way to explain the results ) )


Here is a quick demo of how
_ my ____arrOut()=Match(arrArg1(), arrIn() , 0 ) ____ works
Ordinarily, or most usually the first argument is just one thing that you are looking for. As far as I know all documentation tells you that the way Match in Excel works is, ( simplified ) :
_... you look in the second argument array of things for the thing in the first argument, and , assuming you find it, return the position along where it is, pseudo like
_____ Match( b , { a, b, c } , 0 ) = 2
In the practice we sometimes, ( not always ) , find that things in Excel will work with array arguments and return a corresponding array of outputs. So taking that last example, pseudo like
_____ Match( {b, a} , { a, b, c } , 0 ) = {2, 1}

So that is a bit of theory out of the way. ( I have done a fuller explanation in a few places of how the Application.Index with Look Up Rows and Columns Arguments as VBA Arrays works in a few places
https://excelfox.com/forum/showthrea...ll=1#post16455
https://www.excelforum.com/excel-new...ml#post4571172 )

---

Part 2. Here is my solution examples
Refering to the first long macro below:

Rem1 is just making some stuff I need for the demo. I use the string example of “ZAC” as per the original OP example http://www.eileenslounge.com/viewtopic.php?f=30&t=38460 . For reasons given in the next bit, I make an array of the 26 Ascii Code numbers for the capital alphabet characters, A, B. C ….Z , Asskeys() = { 65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81 ,82,83,84,85,86,87,88,89,90 }
My array of the weights values, Weights(), for the characters will be the same size as Asskeys() and will have the corresponding weight value for each of the 26 characters in the same order.
Once again it will be clear why later. For now, the point is to have arrays of the same size with related things in the same order

Code:

---

' '  Ascii Code      65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90
' '                    A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z
' Let Weights() = Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2) 

---

Rem 2
I found a way on the internet to turn my string example into an array of single characters, which is what I will be feeding into my Match as first argument. ( Unfortunately it does not return in each element the character, but rather its Ascii Code. But for my purposes that’s just as good.

Rem 3 Match
This is the Match bit, and it tells me the position along where I find the three Ascii Code numbers of “ZAC” in the Ascii Code array, Asskeys()
We get from match here, a 3 element array, MtchRes(), of the position along, of the characters in “ZAC” in the array Asskeys(). We have organised that the array of weights is organised in the same order, so this will also be the position along of the corresponding weight number in the array of weights, Weights().
In the example we should have then an array like {26, 1, 3} _ ( if you have followed the logic so far, you can see this is like a pseudo Alphabet position of the characters, Z , A , and C __ (But don’t get confused with Ascii codes, which is pseudo like the official position of characters, and defined by some world standard, that Excel knows about. As example, capital A is listed as Ascii code 65, lowercase a is listed as 97 )

Rem 4 Index
The 3 element array of the position along, of the characters in “ZAC” in the array Asskeys(), is effectively the Clms() array we need for a __arrOut()=Index(ArrIn(), Rws(), Clms())__type solution, where the look up array, arrIn() , will be the weights array, Weights()
The returned array from Index , arrOut(), will be an array, of 3 numbers, which are the weight numbers for the example string “ZAC”.

Rem 5
Finally we simply sum the elements of the found weight values, as per the original OP request.

Code:

---

Sub AssKeys()
Rem 1 Make the arrays and other hard coded things for the demo
Dim AssKeys(1 To 26) As Long
Dim Eye As Long
    For Eye = 65 To 90 Step 1
    Let AssKeys(Eye - 64) = Eye
    Next Eye
' OR
'  Dim AssKeys() As Variant: Let AssKey() = Array(65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90)
Dim Weights() As Variant:
 '  Ascii Code      65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90
 '                    A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z
 Let Weights() = Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2)
Dim ZAC As String
 Let ZAC = "ZAC" ' This is a demo example text string
Rem 2 String to array
Dim arrZAC() As Byte: Let arrZAC() = StrConv(ZAC, vbFromUnicode) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Rem 3 Match
Dim MtchRes() As Variant
 Let MtchRes() = Application.Match(arrZAC(), AssKeys(), 0)
Rem 4 Index
Dim arrOut() As Variant
 Let arrOut() = Application.Index(Weights(), 1, MtchRes())
Rem 5
Dim Some As Long: Let Some = Application.Sum(arrOut())
End Sub

---

Here the shortening possibilities

Code:

---

Sub BeautifulAsskeys()
Rem 1 Make the arrays and other hard coded things for the demo
'Dim Asskeys(1 To 26) As Long
'Dim Eye As Long
'    For Eye = 65 To 90 Step 1
'    Let Asskeys(Eye - 64) = Eye
'    Next Eye
' OR
'  Dim AssKeys() As Variant: Let AssKey() = Array(65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90)
'Dim Weights() As Variant:
' '  Ascii Code      65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90
' '                    A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z
' Let Weights() = Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2)
'Dim ZAC As String
' Let ZAC = "ZAC" ' This is a demo example text string
Rem 2 String to array
Dim arrZAC() As Byte: Let arrZAC() = StrConv("ZAC", vbFromUnicode) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Rem 3 Match
'Dim MtchRes() As Variant
' Let MtchRes() = Application.Match(arrZAC(), Asskeys(), 0)
' Let MtchRes() = Application.Match(StrConv(ZAC, vbFromUnicode), Asskeys(), 0)' this does not work
Rem 4 Index
'Dim arrOut() As Variant
' Let arrOut() = Application.Index(Weights(), 1, MtchRes())
Rem 5
Dim Some As Long: Let Some = Application.Sum(Application.Index(Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2), 1, Application.Match(arrZAC(), Array(65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90), 0)))
End Sub
'
Sub AsKeys()                                                      '  http://www.eileenslounge.com/viewtopic.php?p=297288#p297288
Dim arrZAC() As Byte: Let arrZAC() = StrConv("ZAC", vbFromUnicode) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Dim Some As Long: Let Some = Application.Sum(Application.Index(Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2), 1, Application.Match(arrZAC(), Array(65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90), 0)))
End Sub


---

---

** I use the word “my” lightly. – I learnt all this stuff from looking at stuff from Rick Rothstein and snb. ( I am not sure if they “invented it” , or got it from other peoples stuff. if I added anything “new” , it might be some of my detailed explanations, which whilst I don’t know if they are correct, they seem to be a valid theory as they go a long way to explain the results

Spare post for sharing some documents
Ersatzpost für die gemeinsame Nutzung einiger Dokumente


https://i.postimg.cc/c6gJFyYS/Skizze...kizze-2-ii.jpgAttachment 3932
https://i.postimg.cc/WpSJbs1h/Skizze...kizze-2-ii.jpg

https://i.postimg.cc/Lhc87CBZ/Skizze-2-i.jpgAttachment 3933
https://i.postimg.cc/Wb3qdxdS/Skizze-2-i.jpg

https://i.postimg.cc/D4Xy0LFZ/Skizze-2-ii.jpgAttachment 3934
https://i.postimg.cc/rshdhNPS/Skizze-2-ii.jpg

---

AlanSdattHof15Aug2022.docx
https://app.box.com/s/xxntrig04ppe7hdd68se882rm3fv2ysw https://bit.ly/3QXSEv0

AlanSdattHof15Aug2022.doc
https://app.box.com/s/pt4v46nl28qya2bfycgq2rdfz3a35fq8 https://bit.ly/3STVvqm





This post links
AlanSdattHof15Aug2022.docx
AlanSdattHof15Aug2022.doc
https://excelfox.com/forum/showthrea...ll=1#post16655
https://excelfox.com/forum/showthrea...ge55#post16655
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16655&viewfull=1#post16655
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page55#post16655
https://bit.ly/3AEPlmM https://bit.ly/3wmjQvh

In support of this main forum post
https://excelfox.com/forum/showthrea...-Excel-Formula

Assuming this is data and wanted results from column D

_____ Workbook: DogsNutsFormulas.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	Code	Values		Code	Value1	Value2	Value3	Value4
2	1001	2101		1001	2101	5205	2605	9285
3	1001	5205		2604	4256	7458	3555
4	1001	2605
5	1001	9285
6	2604	4256
7	2604	7458
8	2604	3555
9

Worksheet: SimpleUniqueVLookUp



Enter this In D2 via CSE, then drag it down:
=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)),"")

Row\Col	D
2	=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)),"")
3	=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$8),0)),"")

Alternative formula for earlier versions of Excel:
=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0))),"",INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)))

Row\Col	D
2	=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0))),"",INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)))
3	=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$8),0))),"",INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$8),0)))

Hi Amelynn

Quote:

---

Originally Posted by Amelynn

…..

• If after TextBox(n) it is necessary to put the .text or .value
• If not putting anything is wrong (although it works)
• What is the difference between the three things……

---

I can’t answer most of your question here, unfortunately, because
_ I have no experience with Textboxes
_ I am not too familiar with the .Text property.

I can only tell you the small part that I know about: - what I know about is
Range__
Range__.Value
Range__.Value2

Range__ object
Range__ is an object with an extremely large number of properties, methods and various things.
Range__ is all to do with how Excel organises and uses cells. Understanding the Range__ object is probably one of the most important things to know about in Excel and VBA, especially if you are interested in spreadsheet things.
But many times, we are only interested in the value that is in a cell. Because most people are often only interested in the value in a cell, Microsoft have made .Value the default of what you get if you just use Range__. So most of the time if you choose to write just Range__ , then in fact , Excel will not see that, instead it will see and use Range__.Value
It is just personal choice if you choose to use Range__.Value or Range__. Usually there are no problems if you just use Range__ , but I have seen occasions when this caused problems as there may be occasions when Excel tries to refer to the Range__ object instead of the value.
So personally I prefer to always include the .Value if I am interested in a value. I will only leave out the .Value if I am doing something that wants me to reference the Range__ object. Just personal choice.

So, in your example, when you used Worksheets("Sheet1").Range("B10") , Excel did not see and use that.
Instead, Excel saw and used this: Worksheets("Sheet1").Range("B10").Value

Quote:

---

Originally Posted by Amelynn

....After the range, should I put .text, .value or just nothing? ...

---

So in your examples you could probably just use nothing , but I personally would recommend that you include .Value ( or .Value2 )
But that is just my personally recommendation

.Value or .Value2
.Value is almost the simplest cell value. But not quite. If you are interested in dates or currency, then .Value will show you the date or currency in a date or currency format.
.Value2 is the most simplest cell value as Excel has it held before any formatting is done.

Personally I will use .Value2 most of the time, because it may work a little faster or may be less likely to problems caused by awkward cell formatting issues. I think theoretically it is also a bit more efficient to use .Value2

So….

Quote:

---

Originally Posted by Amelynn

.....If not putting anything is wrong (although it works).....

---

It is not wrong to put nothing. But it is bad practice, as it may cause problems in other situations in Excel VBA
(More than half of people put nothing, and they will often get a problem later that they don’t understand )

---

Quote:

---

Originally Posted by Amelynn

....Should I necessarily declare that, for example, "Niebla" is a variable of type Str?

---

The way that you are using "Niebla" in VBA coding is perfectly alright, because: Most of the time in VBA coding, if VBA sees something enclosed in quotes, _ "__" _ , like
"xyz"
, then VBA will take the value of _ xyz _ to be a string.
Even if , in your coding, you did this
"3"
, the VBA would not take the FONT=Courier New]"3"[/FONT] as a number. It would see it as a string, just as it would see this as a string
"I have 3 Apples"


Note that VBA is very user friendly with numbers and strings. For example if you pass it a string like "3" in a function wanting a number, the VBA will not error, but instead it will take a number 3 instead.

In many other computer languages you must be much more careful in defining precisely variable types.

---

That is as close as I can come to answering your question.
But I do know about Range__ , Range__.Value , Range__.Value2 quite well.
So I am happy to give you any further clarity on those things. Those things are all to do with range referencing in Excel and VBA, which is a very important thing to know about.


Alan

---

Ref
https://fastexcel.wordpress.com/2011...w-to-avoid-it/

Some notes in support of this forum main post
https://excelfox.com/forum/showthrea...ll=1#post16712


An erroring formula: https://i.postimg.cc/nzbWtGTQ/An-erroring-formula.jpg
https://i.postimg.cc/mcjjYMvb/An-erroring-formula.jpg

Highlight a section, such as the Match section https://i.postimg.cc/5NxZ0pJB/Highlight-match.jpg
https://i.postimg.cc/K4H9pPRj/Highlight-match.jpg

Hit key F9 https://i.postimg.cc/7P1FwdFP/Hit-ke...esult-is-5.jpg
https://i.postimg.cc/Thw7c72B/Hit-ke...esult-is-5.jpg

This reveals that we have 5 , or in total , 5 + 1 = 6


So in the VLookUp we are looking at column 6 in the range given by bk
The names manager tells us which range we want:
Second formula section is the range bk https://i.postimg.cc/JhBFg23L/range-bk.jpg
https://i.postimg.cc/zL8pHpPt/range-bk.jpg



Similarly we can investigate the first section in the erroring formula
Highlight first formula section https://i.postimg.cc/902syY7j/Highli...la-section.jpg

Hit key F9 https://i.postimg.cc/wvLStG3V/Hiut-F...st-section.jpg

This reveals that the Look up value, the value that you are looking for is "DMG1"


Further investigations by trial and error , reveals that some character combinations in cell A2 cause the error. But I do not know why yet ?

Examples:
Not work:
https://i.postimg.cc/YqbTmg2J/d-not-work.jpg
https://i.postimg.cc/d0zbVYQ3/d-MG1-not-work.jpg
Works
https://i.postimg.cc/3xq6tTv1/MG1-works.jpg

In fact, it seems that some character combinations are not allowed as the Look Up value generally , for example try another cell, and I can find a character combination that does not work
https://i.postimg.cc/SK204shH/Not-wo...ters-in-A6.jpg
https://i.postimg.cc/Ff4Cgh1x/Not-wo...ters-in-A6.jpg

I am puzzled.

In fact if you look in detail at the results you are getting when it does appear to work, then they are not alwaysthe correct values.
https://postimg.cc/kBnt3Zzg
https://i.postimg.cc/kBnt3Zzg/Wrong-...m-VLook-Up.jpg https://i.postimg.cc/MKB0RJYV/Wrong-...m-VLook-Up.jpg

Very strange. I am even more puzzled
?????

---

---

Update Answer from Sandy https://excelfox.com/forum/showthrea...ll=1#post16717

Quote:

---

Originally Posted by sandy666

with formula =VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,0) value $1.55 is returned and so on
..just forgot comma on the end or define last argument 0, VLOOKUP(F$26,bk,MATCH($B$24,bkt,1)+1,) so he need to learn how to use VLOOKUP function
VLOOKUP require all arguments, even if last argument is omitted there should be defined place for it after last comma

---

https://i.postimg.cc/ncHVbyD8/ThatsIt.gif
https://i.postimg.cc/15VpN7Hj/ThatsIt.jpg https://i.postimg.cc/N5Yy34Z6/ThatsIt.jpg
( I thought I had checked that, but missed something somewhere, I don’t know why I missed that, maybe I think also I need to learn how to use VLoopUp properly! )

---

---

Test

I see what you are saying its like “the 14th thing along” , like in if it was in a 1 dimensional or 1 “row” array,
This sort of thing
_number along{1 _ , 2 , 3 , 4 , 5 , 6 , 7 , 8 _ , 9 _ , 10_, 11_, 12_,13 , 14 __ , 15 , _16}
____INDEX({"Part",13,25,37,48,73,101,145,201,289,600,7 50,1009, "DMG1",4.1,2.85},1,14) = "DMG1"
Or similarly
__number along_____{1 _ , 2 , 3 , 4 , 5 , 6 , 7 , 8 _ , 9 _ , 10_, 11_, 12_,13 , 14 __ , 15 , _16}
__=MATCH("DMG1",{"Part",13,25,37,48,73,101,145,201,289,600,750,10 09, "DMG1",4.1,2.85},0) = 14
(Note I must change the row separating ; before the to a column separating , **

I also get indexes mixed up and also I mix up item numbers and indexes. Sometimes they are the same, sometimes they have a similar meaning



( ** just passing interest, if in a range we talk about the “number along” on any range, then we are talking about the range item number, and this “number along” goes like all columns in a row, then next row and so on.
So example in both these ranges, the range item number is 14

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Part	13	25	37	48	73	101	145	201	289	600	750	1009	DMG1

Range("A1:N1").Item(14) = "DMG1"

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Part	13	25	37	48	73	101	145	201	289	600	750	1009
2	DMG1	$ 4.10	$ 2.85	$ 2.85	$ 1.90	$ 1.55	$ 1.35	$ 1.25	$ 0.95	$ 0.85	$ 0.75	$ 0.75	$ 0.65

Range("A1:M2").Item(14) = "DMG1"

Row\Col	O	P	Q	R	S	T	U	V	W
4	Part	13	25	37	48	73	101	145	201
5	289	600	750	1009	DMG1	$ 4.10	$ 2.85	$ 2.85	$ 1.90

Range("O4:W5").Item(14) = "DMG1"

Here is a very interesting article by a very clever person on all that https://excelfox.com/forum/showthrea...column-looping )

In support of this forum thread
https://excelfox.com/forum/showthrea...ll=1#post16718

In worksheet String to search is the following:

_____ Workbook: VBA delete everything between two newlines where the string appears.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
1
2	paragraph/line
3
4	searched string
5	#VBA

Worksheet: String to search

Here is the given input examples ( The OP gave 4 cells , but for demo here I will put them all in the same code box and separate the 4 examples with a line. Black is the before/input , and Blue the after/ wanted output

Code:

---

in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in VBA"

if the given string is found in any paragraph/line excel cell then delete everything between two newlines where the string appears. in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in VBA"searched*string

if the given string is found in any excel cell then delete everything between two newlines where the string appears.
in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in VBA"




if the given string is found in any excel cell then delete everything between two newlines where the string appears.

---

in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in #VBA"
if the given string is found in any excel cell then delete paragraph/line everything between two newlines where the string appears.
in VBA if the given string is found then delete paragraph/line everything between two newlines where the string appears.
"Looking for help in VBA"
if the given string is found in any excel cell then delete everything between two newlines where the string appears.
searched*string
in VBA if the given string is found then delete everything between two newlines where the string appears.


"Looking for help in VBA"
if the given string is found in any excel cell then delete everything between two newlines where the string appears.

---

in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in VBA"

if the given string is found in any paragraph/line excel cell then delete everything between two newlines where the string appears. in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in VBA"searched*string

if the given string is found in any excel cell then delete everything between two newlines where the string appears.
in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in VBA"




if the given string is found in any excel cell then delete everything between two newlines where the string appears.

---

in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in #VBA"
if the given string is found in any excel cell then delete paragraph/line everything between two newlines where the string appears.
in VBA if the given string is found then delete paragraph/line everything between two newlines where the string appears.
"Looking for help in VBA"
if the given string is found in any excel cell then delete everything between two newlines where the string appears.
searched*string
in VBA if the given string is found then delete everything between two newlines where the string appears.


"Looking for help in VBA"
if the given string is found in any excel cell then delete everything between two newlines where the string appears.

---

OK so it looks quite clear what is needed. The OPs explanation does confirm to this, …. if the given string is found in any excel cell then delete everything between two newlines where the string appears.
… How can I delete sentences with a specific keyword in excel cells in all sheets?
…..but without the example it was much more difficult to understand and may have been interpreted to mean something else..

---

Putting the explanation of what is wanted a bit clearer, …
There is text in some cells. The text is split into paragraphs or lines, that is to say we see multi-line text, something like

Code:

---

How are you?

Well I am OK Bro today
Enjoying a wank just now.

---

What we want to do is: Modify the text in cells in this way: If certain words are found in a paragraph or line, then all the text in that line needs to be deleted. Lets say in that example, Bro was one of the words to search for. In that case, the text should be modified so as to look like this

Code:

---

How are you?


Enjoying a wank just now.

---

We remove all the text, Well I am OK Bro today, but it looks like the lines stay there.


We are manipulating text and some form of line separation is involved, so its usually a good idea to look carefully at the actual characters: Almost immediately I think of Splitting and Joining text where the thing to split by or use to join together will be a text line separator, which can vary. Hence important to take a look, so I will, here, later
https://excelfox.com/forum/showthrea...ll=1#post16727

In support of this main Forum Thread
https://excelfox.com/forum/showthrea...earched-string

Input

Code:

---

                                       
"#VBA: keep only one searched string searched string searched string searched string.
#VBA: keep only searched string one searched string.
The list of search stings searchedCSV string searched string is given in sheet 2.
In sheet1, with #VBA check if a given string is CSV searched string found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with CSV searched string multiple column data in multiple rows as well as in a CSV or text file."                               
                               
"searched string searched string #VBA: keep only one searched string.
#VBA: keep only one searched string.
searched string searched string searched stringThe list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than once #VBA #VBA #VBA #VBA #VBA #VBA #VBA #VBA #VBA #VBA #VBA #VBA in a paragraph/line. If found more than once then keep only one. #VBA #VBA #VBA #VBA
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."                               
                               
"#VBA: keep only one searched string.
#VBA: keep only one searched string.
The list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file. #VBA: keep only one searched string.
#VBA: keep only one searched string. searched string searched string searched string searched string
The list of search stings is given in sheet 2. searched string searched string searched string searched string
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."                               
                               
                               
                               
                               
        "#VBA: #VBA  keep only one searched string.
#VBA: #VBA #VBAkeep only one searched string.
              searched string searched string  searched string  searched string  searched string
In sheet1, with #VBA check if a given string is found more than #VBA once in a paragraph/line. If found more than #VBA once then keep only one.

#VBA: keep only one searched string.
The list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than #VBA once in a paragraph/line. If found more than #VBA once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."                       
                               
                               
                                "#VBA: keep only one searched string.


In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file. #VBA: keep only one searched string.


In sheet1, with #VBA check if a  paragraph/line given string is found more than once in a paragraph/line paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."

---

Output

Code:

---

                               
"#VBA: keep only one searched string .
#VBA: keep only searched string one .
The list of search stings searchedCSV string searched string is given in sheet 2.
In sheet1, with #VBA check if a given string is CSV searched string found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with CSV searched string multiple column data in multiple rows as well as in a CSV or text file."                               
                               
"searched string  #VBA: keep only one searched string.
#VBA: keep only one searched string.
searched string searched stringThe list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."                               
                               
"#VBA: keep only one searched string.
#VBA: keep only one searched string.
The list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file. #VBA: keep only one searched string.
#VBA: keep only one searched string. 
The list of search stings is given in sheet 2. searched string
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."                               
                               
                               
                               
                               
        "#VBA:  keep only one searched string.
#VBA: keep only one searched string.
              searched string
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.

#VBA: keep only one searched string.
The list of search stings is given in sheet 2.
In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."                       
                               
                               
                                "#VBA: keep only one searched string.


In sheet1, with #VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file. #VBA: keep only one searched string.


In sheet1, with #VBA check if a  paragraph/line given string is found more than once in a paragraph/line paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file."

---

In support of this thread
https://excelfox.com/forum/showthrea...ll=1#post16710

Sheet1 Input in column A
_____ Workbook: VBA row to cell1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
2	234. digital marketing information: 2021-2022***….Keywrod1: 2021-2022***
3
4	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
5	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
6
7
8	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
9
10	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
11
12	Keywrod1:
13	Keyword2: 30000000 *&^%$QWERTY QWERTY
14
15
16	2344. digital marketing information: 2020-2021***….Keywrod1: *** 2020-2021 digital marketing information digital marketing information
17
18	Digital marketing: = also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
19	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
20
21	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
22	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
23
24	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
25	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
26	Keywrod1: *** 2020-2021
27	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
28	Keyword2
29
30	1. digital marketing information: 2021-2022***….Keywrod1: 2021-2022***
31
32	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
33
34
35	QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

Worksheet: InputSheet1

Code:

---

"234. digital marketing information: 2021-2022***….Keywrod1:
2021-2022***"

"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel."
"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
"


"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel."

"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel."

Keywrod1:
Keyword2: 30000000 *&^%$QWERTY QWERTY


"2344. digital marketing information: 2020-2021***….Keywrod1: *** 2020-2021
digital marketing information
digital marketing information"

"Digital marketing: =
also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel."
"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
"

"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
"
"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel."

"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
"
"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel."
Keywrod1: *** 2020-2021
"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel."
Keyword2

"1. digital marketing information: 2021-2022***….Keywrod1:
2021-2022***"

"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel."


QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

---

Sheet2 Wanted output, next post

Output required ( https://excelfox.com/forum/showthrea...ll=1#post16685 )

_____ Workbook: VBA row to cell1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
2	234. digital marketing information: 2021-2022***….Keywrod1: 2021-2022*** Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Keywrod1: Keyword2: 30000000 *&^%$QWERTY QWERTY
3	2344. digital marketing information: 2020-2021***….Keywrod1: *** 2020-2021 digital marketing information digital marketing information Digital marketing: = also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel." "Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel." Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel." Keywrod1: *** 2020-2021 Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Keyword2
4	1. digital marketing information: 2021-2022***….Keywrod1: 2021-2022*** Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

Worksheet: OutputSheet2

Code:

---

"234. digital marketing information: 2021-2022***….Keywrod1:
2021-2022***

Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.


Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.

Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.

Keywrod1:
Keyword2: 30000000 *&^%$QWERTY QWERTY"
"2344. digital marketing information: 2020-2021***….Keywrod1: *** 2020-2021
digital marketing information
digital marketing information

Digital marketing: =
also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.""
""Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.


Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.

Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.""

Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.

Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.""
Keywrod1: *** 2020-2021
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Keyword2"
"1. digital marketing information: 2021-2022***….Keywrod1:
2021-2022***

Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.


QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY"

---

Further info for the last two posts

Input ( Sheet1)

_____ Workbook: VBA row to cell1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
30	1. digital marketing information: 2021-2022***….Keywrod1: 2021-2022***
31
32	Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel.
33
34
35	QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

Worksheet: InputSheet1

Code:

---

"1. digital marketing information: 2021-2022***….Keywrod1:
2021-2022***"

"Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel."


QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

---

Output Sheet 2

_____ Workbook: VBA row to cell1.xlsx ( Using Excel 2007 32 bit )

Row\Col

A

4

1. digital marketing information: 2021-2022***….Keywrod1: 2021-2022*** Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. Digital marketing, also called online marketing, is the promotion of brands to connect with potential customers using the internet and other forms of digital communication. This includes not only email, social media, and web-based advertising, but also text and multimedia messages as a marketing channel. QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY

Worksheet: OutputSheet2

Code:

---

"1. digital marketing information: 2021-2022***….Keywrod1:
2021-2022***

Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.
Digital marketing, also called online marketing, is the promotion of brands to
connect with potential customers using the internet and other forms of digital communication.
This includes not only email, social media, and web-based advertising, but also
text and multimedia messages as a marketing channel.


QWERTY Keyword2: 30000000 *&^%$QWERTY QWERTY"

---

---

---

---

Test

Sept 14 https://www.youtube.com/watch?v=U76ZRIzBhOA https://bit.ly/3EuOh7B
May 14 https://www.youtube.com/watch?v=tPRv-ATUBe4 https://bit.ly/3rMAaTw
May 6 https://www.youtube.com/watch?v=7KPH...baD5Vd&index=5 https://bit.ly/3TtTlNV
May 4 https://www.youtube.com/watch?v=nDlGkDENCyM https://bit.ly/3MkNUhC
Apr 29 https://www.youtube.com/watch?v=xqBh...baD5Vd&index=6 https://bit.ly/3ejT2WX

This is post
https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)?p=19586&viewfull=1#post19586
https://excelfox.com/forum/showthrea...ll=1#post19586
https://excelfox.com/forum/showthrea...ge54#post19586
https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)/page54#post19586

---

In support of this main Forum Post http://www.eileenslounge.com/viewtop...301714#p301714

Do the transpose a bit differently using Application.Index

Consider this example of a Selectioned range:

a	b
c	d
e	f

,and we want an output of some form like a b c d e f
Consider first a single column such as the first,
Let vTemp = Application.Transpose(Selection.Columns(1)) ' gives - {"a", "c", "e"}
We can Do the transpose a bit differently using Application.Index
Let vTemp = Application.Index(Selection, Array(1, 2, 3), Array(1, 1, 1)) ' - {"a", "c", "e"}
What's going on: Excel / Excel VBA is doing what it often does, along a row, then down a column, sometimes referred to as array type calculations, in this case the argument arrays are followed leading to an output of a form of the 1 dimensions, ("pseudo horizontal") array , as we want. The index works three times on each pair of co ordinates, each time giving the result in the way Index would in the more conventional way for just 1 pair of row and column co ordinates
Using this index way we are not restricted to a single column, we can pick any co ordinates we chose.
The next co ordinates give us a simple single line of all our cell values
Let vTemp = Application.Index(Selection, Array(1, 1, 2, 2, 3, 3), Array(1, 2, 1, 2, 1, 2)) ' {"a", "b", "c", "d", "e", "f"}
' Or
Dim Rws() As Variant, Clms() As Variant
Let Rws() = Array(1, 1, 2, 2, 3, 3): Clms() = Array(1, 2, 1, 2, 1, 2)
Let vTemp = Application.Index(Selection, Rws(), Clms()) ' ' {"a", "b", "c", "d", "e", "f"}

To make a more useful flexible solution, what we need to do is to get those array arguments dynamically from, in this example, the Selection
So on the face of it, it is quite easy what we need to do: It is usually just some seemingly clever, but actually quite basic maths. It is helpful perhaps to get in the head where/ why the problem / need for the maths comes in. It’s the difference between computers and us: Computers keep going, often things are listed or numbered in sequential number s ( example see item number way of doing things , https://excelfox.com/forum/showthrea...column-looping ) but we use a paper/book or screen so keep going back/ (returning to the left) then up/down, (“Line feeding”)
In the various maths, the row count would usually feature less, or will feature in a similar way for slightly different examples, since mainly it effects ( along with the column count ) a final total number. In some examples the total number may be used and so the row count may never feature in any maths.
The column count is more prominent, since this size restriction is the human wanted chopping up of things to get them within our limited view, ( Generally we have a more limited width ( column ) than length (row) perception: we scroll more hapilly down than across. I don’t. My brain is more open minded and wide.
So we should be thinking more in terms of column count effecting things

Consider the requirement for the Rws()
For the case of more than one column, the sequential numbers need to be repeated for as many times as we have columns. So it is possibly a good guess that some division of that column count would be useful. Doing that division will give us
{.5, 1, 1.5, 2, 2.5, 3}
Observation of that shows we see something similar in the whole part of the numbers to what we want. But we don’t quite get what we want by taking the integer. We get
{0, 1, 1, 2, 2, 3}
We need to do something to correct what we get to what we want. It may not be obvious what we should do. If we consider another example it might help. So let’s consider 3 columns. Applying the same logic we would get
{0, 0, 1, 1, 1, 2}
It seems that the numbers which are to be dived by the column count and then taken the integer of, should not be {1, 2, 3, 4, 5, 6} , but for the case of 2 columns
{2, 3, 4, 5, 6, 7}
, and for the case of 3 columns
{3, 4, 5, 6, 7, 8}
So it looks like we want to add (ColumnCount-1) before the integer is done

Consider the requirement for the Clms()
We want to keep repeating the column sequence. The Mod function is promising for this, since it gives us what is left over after taking off as many as possible of the given number in the second argument. So whatever this gives us, will be repeated .
This, Mod(Column(A:F),2) , almost gets us there with {1, 0, 1, 0, 1, 0} and checking the same logic for 3 columns we have {1, 2, 0, 1, 2, 0}
We need to tweak that to get us to start at 0 and then add 1

_.______


Those ideas are incorporated into the full code version in the next post, https://excelfox.com/forum/showthrea...ll=1#post19591

Ref:
http://www.excelforum.com/excel-prog...umn-range.html
http://www.excelforum.com/excel-prog...e-columns.html

Coding for last post

Code:

---

' https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)?p=19586&viewfull=1#post19586
' http://www.eileenslounge.com/viewtopic.php?f=27&t=38973&p=301714#p301714
Sub TransposeABitDifferent()
' Consider a two column, three row selection
 '  a  b
 '  c  d
 '  e  f
Dim vTemp As Variant ' Use variant, and set a  Shift+F9  watch on it ( To do this: Highlight it anywhere in the coding and use keys  Shift+F9  )
' A single column transpose
 Let vTemp = Application.Transpose(Selection.Columns(1))          '  gives  - {"a", "c", "e"}
' Or  we can  Transpose in a different way, with index, and Stuff
 Let vTemp = Application.Index(Selection, Array(1, 2, 3), Array(1, 1, 1))  ' - {"a", "c", "e"}
' What's going on: Excel / Excel VBA is doing what it often does,  along a row, then down a column, sometimes referred to as array type calculations, in this case the argument arrays are followed leading to an output of a form of the 1 dimensions, ("pseudo horizontal") array , as we want. The index works three times on each pair of co ordinates, each time giving the result in the way Index would in the more conventional way for just 1 pair of row and column co ordinates
' using this way we are not restricted to a single column, we can pick any co ordinates we chose.
' The next co ordinates give us a simple single line of all our cell values
Let vTemp = Application.Index(Selection, Array(1, 1, 2, 2, 3, 3), Array(1, 2, 1, 2, 1, 2)) ' {"a", "b", "c", "d", "e", "f"}
' Or
Dim Rws() As Variant, Clms() As Variant
 Let Rws() = Array(1, 1, 2, 2, 3, 3): Clms() = Array(1, 2, 1, 2, 1, 2)
 Let vTemp = Application.Index(Selection, Rws(), Clms()) '                                ' {"a", "b", "c", "d", "e", "f"}
 
' To make a more useful flexible solution, what we need to do is to get those array arguments dynamically from the  Selection
' For both array aguments we need a 6 element 1 dimensional array
' ( we hit a snag generally in these things in that often Excel has those arrays but won't give us them, - typically it may only give us the first value. Noone is quite sure why. There are various tricks found empirically to make Excel give us the full array of values. Usually it involves putting what we actually want to do inside something that encourages Excel to return us all array values. (There may be some parallel to the so called  C S E  action in a spreadsheet to get full array results, noone is quite sure). Herfe is a trick I found, empirically to often work
'  If({1},  here what you want to do  )      I don't always need to do this. During the development of a solution I monitor ma results in  vTemp  , and if I onbl<y see a single result then I add this enclosing bit to see if that helps get me an array of results

' The start point is usually to get an array of the size we want of integers, and then fiddle with some maths to get the actual integer values we want
 Let vTemp = Evaluate("=Column(A:F)") '    {1, 2, 3, 4, 5, 6}
'  For a flexible solution we want the  F  Getting at a column letter is often a bit tricky, strangely Excel never made a function for it, whereas getting the column number is usually easy.
'  In our case the column numnber is given by  Selection.clumns.count
 Let vTemp = Selection.Cells.Count  '  6
' there are a few ways to convert that to the appropriat Letter. An address way is convenient
 Let vTemp = Split(Cells(1, 6).Address, "$")(1) '  -  "F"        This splits any row cell in column 6 address, in this example the cell $F$1, by a  "$"  resulting in an array  {"", "F", "1"), we thne take the second element, which has the indice of  1  , (not  2  ,since  such an array starts at the indicie of  0)
 Let vTemp = Split(Cells(1, Selection.Cells.Count).Address, "$")(1)  '  - "F"
 
 Let vTemp = Evaluate("=Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")") '    {1, 2, 3, 4, 5, 6}
' ( To make the next steps easy to follow, we will stay with the  "F"  hard coded then substitute the bit to get it flexible later
 Let vTemp = Evaluate("=Column(A:F)") '    {1, 2, 3, 4, 5, 6}
' Some maths now. There are probably a few ways. We fiddle around a bit.  We try to get it using some numbers we could get dynamically, things typically of the count nature, such as row and column count, which are  3  and  2  in this example
' Rws()
 Let vTemp = Evaluate("=Column(A:F)/2") ' {.5, 1, 1.5, 2, 2.5, 3}
 Let vTemp = Evaluate("=Int(Column(A:F)/2)") ' 0
 Let vTemp = Evaluate("=If({1},Int(Column(A:F)/2))")  '  {0, 1, 1, 2, 2, 3}
 Let vTemp = Evaluate("=Int((Column(A:F)+2)/2)") ' 1
 Let vTemp = Evaluate("=If({1},Int((Column(A:F)+2)/2))") '
 Let vTemp = Evaluate("=If({1},Int((Column(A:F)+(2-1))/2))")
 Let vTemp = Evaluate("=If({1},Int((Column(A:F)+(" & Selection.Columns.Count & "-1))/" & Selection.Columns.Count & "))")
 
 Let vTemp = Evaluate("=If({1},Int((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")+(" & Selection.Columns.Count & "-1))/" & Selection.Columns.Count & "))")
 Let Rws() = Evaluate("=If({1},Int((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")+(" & Selection.Columns.Count & "-1))/" & Selection.Columns.Count & "))")

' Clms()
 Let vTemp = Evaluate("=Mod(Column(A:F),2)") ' 1
 Let vTemp = Evaluate("=If({1},Mod(Column(A:F),2))")      ' {0, 1, 0, 1, 0, 1}
 Let vTemp = Evaluate("=If({1},Mod((Column(A:F)-1),2)+1)") ' {1, 2, 1, 2, 1, 2}
 Let vTemp = Evaluate("=If({1},Mod((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")-1),2)+1)")
 Let Clms() = Evaluate("=If({1},Mod((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")-1),2)+1)")


Let vTemp = Application.Index(Selection, Rws(), Clms()) '                                ' {"a", "b", "c", "d", "e", "f"}

' Do the Join
Dim StrOut As String
 Let StrOut = Join(vTemp, ";"): Debug.Print StrOut  '      a;b;c;d;e;f
End Sub
'
'
' Ref
' http://www.excelforum.com/excel-programming-vba-macros/1138428-multidimensional-array-to-single-column-range.html
' http://www.excelforum.com/excel-programming-vba-macros/1138627-dividing-the-items-of-an-array-over-multiple-columns.html

Sub SnberOne()  '  http://www.eileenslounge.com/viewtopic.php?p=301714&sid=4705abb7ec796b7a3426c78642d4f638#p301714
 Let Selection.Resize(1, 1).Offset(0, Selection.Columns.Count).value2 = Join(Application.Index(Selection, Evaluate("=If({1},Int((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")+(" & Selection.Columns.Count & "-1))/" & Selection.Columns.Count & "))"), Evaluate("=If({1},Mod((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")-1),2)+1)")), VBA.InputBox("separator", , ";")) '      a;b;c;d;e;f
End Sub

---

Some notes for this main forum post
https://eileenslounge.com/viewtopic.php?f=27&t=39588


https://postimg.cc/RqMKRrNz
https://i.postimg.cc/RqMKRrNz/Testin...4434444344.jpg
_____ Workbook: report.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	I	J	K	L	M
1	DATA						REQUIRE
2	DIVISION	POSITION	POSITION REPORTING	LEVEL_NO	empno	code	DIVISION	LEVEL_NO	POSITION	empno	code
3	XX	OZ00301	INDOL	1	E1	LL81	XX	1	OZ00301	E1	LL81
4	XX	LR0201	OZ00301	2	E2	LL82	XX	2	LR0201	E2	LL82
5	XX	LA0101	LR0201	3	E3	LL83	XX	3	LA0101	E3	LL83
6	XX	LA0201	LR0201	3	E4	LL84	XX	4	XX0101	E11	LL91
7	XX	LA0701	LR0201	3	E5	LL85	XX	4	XX0102	E12	LL92
8	XX	XX0502	LA0201	4	E6	LL86	XX	4	XX0103	E13	LL93
9	XX	XX0601	LA0201	4	E7	LL87	XX	4	XX0104	E14	LL94
10	XX	XX1901	LA0201	4	E8	LL88	XX	3	LA0201	E4	LL84
11	XX	XX2101	LA0201	4	E9	LL89	XX	4	XX0501	E17	LL97
12	XX	XX2201	LA0701	4	E10	LL90	XX	4	XX0502	E6	LL86
13	XX	XX0101	LA0101	4	E11	LL91	XX	4	XX0601	E7	LL87
14	XX	XX0102	LA0101	4	E12	LL92	XX	4	XX1901	E8	LL88
15	XX	XX0103	LA0101	4	E13	LL93	XX	4	XX2101	E9	LL89
16	XX	XX0104	LA0101	4	E14	LL94	XX	3	LA0701	E5	LL85
17	XX	XX0201	LA0701	4	E15	LL95	XX	4	XX0201	E15	LL95
18	XX	XX0301	LA0701	4	E16	LL96	XX	4	XX0301	E16	LL96
19	XX	XX0501	LA0201	4	E17	LL97	XX	4	XX2201	E10	LL90

Worksheet: Sheet1

sachin483 https://eileenslounge.com/viewtopic....306780#p306780
i have postion code and reporting postion and in 2 column but i want the format of reporting one below another ie :- 4 level will report to 3 and 3 level report to 2 and 2 level will report to 1 if any level is not there then create blank level for upper postion example in attached File



snb @ https://eileenslounge.com/viewtopic....306884#p306884
The crux in the question
Change the order of items from 1,2,3,3,3,4,4,4,4,4,4,4,4,4
to inserting the '4' items after the '3' item it belongs to (where cells(n,3) matches cells(y,2))
Resulting order: 1,2,3,4,4,4,3,4,4,4,3,4,4,4

Alan, a few hours later https://excelfox.com/forum/showthrea...ll=1#post19938
Change this
1,2, 3 , 3 , 3 , 4 , 4 , 4 , 4, 4 , 4 , 4 . 4 , 4 , 4 , 4 , 4
To this
1,2, 3 , 4 , 4 . 4 , 4 , 3 , 4 , 4 , 4 , 4 , 4 , 3 , 4 , 4 , 4

' or


Change this
1
2
3
3
3
4
4
4
4
4
4
4
4
4
4
4
4
To this
1
2
3
4
4
4
4
3
4
4
4
4
4
3
4
4
4

_____ Workbook: report.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	I	J	K	L	M
1	DATA						REQUIRE
2	DIVISION	POSITION	POSITION REPORTING	LEVEL_NO	empno	code	DIVISION	LEVEL_NO	POSITION	empno	code
3	XX	OZ00301	INDOL	1	E1	LL81	XX	1	OZ00301	E1	LL81
4	XX	LR0201	OZ00301	2	E2	LL82	XX	2	LR0201	E2	LL82
5	XX	LA0101	LR0201	3	E3	LL83	XX	3	LA0101	E3	LL83
6	XX	LA0201	LR0201	3	E4	LL84	XX	4	XX0101	E11	LL91
7	XX	LA0701	LR0201	3	E5	LL85	XX	4	XX0102	E12	LL92
8	XX	XX0502	LA0201	4	E6	LL86	XX	4	XX0103	E13	LL93
9	XX	XX0601	LA0201	4	E7	LL87	XX	4	XX0104	E14	LL94
10	XX	XX1901	LA0201	4	E8	LL88	XX	3	LA0201	E4	LL84
11	XX	XX2101	LA0201	4	E9	LL89	XX	4	XX0501	E17	LL97
12	XX	XX2201	LA0701	4	E10	LL90	XX	4	XX0502	E6	LL86
13	XX	XX0101	LA0101	4	E11	LL91	XX	4	XX0601	E7	LL87
14	XX	XX0102	LA0101	4	E12	LL92	XX	4	XX1901	E8	LL88
15	XX	XX0103	LA0101	4	E13	LL93	XX	4	XX2101	E9	LL89
16	XX	XX0104	LA0101	4	E14	LL94	XX	3	LA0701	E5	LL85
17	XX	XX0201	LA0701	4	E15	LL95	XX	4	XX0201	E15	LL95
18	XX	XX0301	LA0701	4	E16	LL96	XX	4	XX0301	E16	LL96
19	XX	XX0501	LA0201	4	E17	LL97	XX	4	XX2201	E10	LL90

Worksheet: Sheet1





Change this
1,2, 3 , 3 , 3 , 4 , 4 , 4 , 4, 4 , 4 , 4 . 4 , 4 , 4 , 4 , 4
To this
1,2, 3 , 4 , 4 . 4 , 4 , 3 , 4 , 4 , 4 , 4 , 4 , 3 , 4 , 4 , 4

' or


Change this
1
2
3
3
3
4
4
4
4
4
4
4
4
4
4
4
4
To this
1
2
3
4
4
4
4
3
4
4
4
4
4
3
4
4
4

Some notes for this main forum post
https://eileenslounge.com/viewtopic.php?f=27&t=39588
This uses something quite smart stumbled across here
https://eileenslounge.com/viewtopic....266691#p266691

If we have a 1 D array of arrays , like form example { {"a", "b"} , { "c", "d" } } , then strangely it acts in our famous App Index Rws() Clms() Magic code line just as if it was an array like this
{"a", "b"
"c", "d" }

Strange , but true.

So in Hans macro from here,
http://www.eileenslounge.com/viewtop...306785#p306785
, or rather the modified one from here ,
http://www.eileenslounge.com/viewtop...306880#p306880
, instead of pasting a 1 D array out each time, so pasting out a line each time, we add that array to an array of arrays, then finally paste out that final array using the App Index Rws() Clms() Magic code line.

Effectively we are doing like this

Code:

---

Sub WonDeeArrayOfArrays() ' https://eileenslounge.com/viewtopic.php?p=266691#p266691
Dim arr1D(1 To 2) As Variant
 Let arr1D(1) = Array("a", "b")
 Let arr1D(2) = Array("c", "d")
Dim arrOut() As Variant
 Let arrOut() = Application.Index(arr1D(), Evaluate("=ROW(1:2)"), Array(1, 2))
 Let arrOut() = Application.Index(arr1D(), Evaluate("=ROW(1:2)"), Evaluate("=COLUMN(A:B)"))
End Sub

---

Code:

---

Option Explicit
Const SourceDivCol = 1
Const SourcePosCol = 2
Const SourceRepCol = 3
Const SourceLevCol = 4
Const SourceEmpCol = 5
Const SourceCodCol = 6
Const TargetDivCol = 15
Const TargetLevCol = 16
Const TargetPosCol = 17
Const TargetEmpCol = 18
Const TargetCodCol = 19
Dim SourceRow As Long
Dim TargetRow As Long
Dim Cnt As Long
Dim WunDeeArrayOfArrays() As Variant



Sub CreateReportHansAlan2() '
 ReDim WunDeeArrayOfArrays(1 To Cells(1).CurrentRegion.Rows.Count - 2)
    Dim Boss As Range
    Dim Adr As String
    Dim Pos As String
    Application.ScreenUpdating = False
    TargetRow = 2
    Set Boss = Columns(SourceLevCol).Find(What:=1, LookAt:=xlWhole)
    Adr = Boss.Address
    Do
        SourceRow = Boss.Row
        TargetRow = TargetRow + 1
    Let Cnt = Cnt + 1: Let WunDeeArrayOfArrays(Cnt) = Application.Index(Cells, SourceRow, Array(SourceDivCol, SourceLevCol, SourcePosCol, SourceEmpCol, SourceCodCol))
'    Let Range("O" & TargetRow & ":S" & TargetRow & "").Value2 = Application.Index(Cells, SourceRow, Array(1, 4, 2, 5, 6))
'    Let Range("O" & TargetRow & ":S" & TargetRow & "").Value2 = Application.Index(Cells, SourceRow, Array(SourceDivCol, SourceLevCol, SourcePosCol, SourceEmpCol, SourceCodCol))
'        Cells(TargetRow, TargetDivCol).Value = Cells(SourceRow, SourceDivCol).Value
'        Cells(TargetRow, TargetLevCol).Value = Cells(SourceRow, SourceLevCol).Value
'        Cells(TargetRow, TargetPosCol).Value = Cells(SourceRow, SourcePosCol).Value
'        Cells(TargetRow, TargetEmpCol).Value = Cells(SourceRow, SourceEmpCol).Value
'        Cells(TargetRow, TargetCodCol).Value = Cells(SourceRow, SourceCodCol).Value
        Pos = Cells(SourceRow, SourcePosCol).Value
        Call AddKids(Pos)
        Set Boss = Columns(SourceLevCol).Find(What:=1, After:=Boss, LookAt:=xlWhole)
        If Boss Is Nothing Then Exit Do
    Loop Until Boss.Address = Adr
    Application.ScreenUpdating = True
 
 Let Range("O3").Resize(Cells(1).CurrentRegion.Rows.Count - 2, 5).Value2 = Application.Index(WunDeeArrayOfArrays, Evaluate("=ROW(1:" & Cells(1).CurrentRegion.Rows.Count - 2 & ")"), Evaluate("=COLUMN(A:E)"))
End Sub

Sub AddKids(BossPos As String) '
    Dim Child As Range
    Dim Adr As String
    Dim Pos As String
    Set Child = Columns(SourceRepCol).Find(What:=BossPos, LookAt:=xlWhole)
    If Child Is Nothing Then Exit Sub
    Adr = Child.Address
    Do
        SourceRow = Child.Row
        TargetRow = TargetRow + 1
    Let Cnt = Cnt + 1: Let WunDeeArrayOfArrays(Cnt) = Application.Index(Cells, SourceRow, Array(SourceDivCol, SourceLevCol, SourcePosCol, SourceEmpCol, SourceCodCol))
'    Let Range("O" & TargetRow & ":S" & TargetRow & "").Value2 = Application.Index(Cells, SourceRow, Array(SourceDivCol, SourceLevCol, SourcePosCol, SourceEmpCol, SourceCodCol))
'        Cells(TargetRow, TargetDivCol).Value = Cells(SourceRow, SourceDivCol).Value
'        Cells(TargetRow, TargetLevCol).Value = Cells(SourceRow, SourceLevCol).Value
'        Cells(TargetRow, TargetPosCol).Value = Cells(SourceRow, SourcePosCol).Value
'        Cells(TargetRow, TargetEmpCol).Value = Cells(SourceRow, SourceEmpCol).Value
'        Cells(TargetRow, TargetCodCol).Value = Cells(SourceRow, SourceCodCol).Value
        Pos = Cells(SourceRow, SourcePosCol).Value
        Call AddKids(Pos)
        Set Child = Columns(SourceRepCol).Find(What:=BossPos, After:=Child, LookAt:=xlWhole)
        If Child Is Nothing Then Exit Do
    Loop Until Child.Address = Adr
End Sub

---

Ref
https://eileenslounge.com/viewtopic....266691#p266691
https://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html#post4571172
https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html#post5408376
https://www.excelforum.com/excel-programming-vba-macros/1328703-copy-1-dim-array-to-and-2-dim-array.html
http://www.eileenslounge.com/viewtopic.php?p=271035#p271035
https://www.ozgrid.com/forum/index.php?thread/1227920-slicing-a-2d-array/&postID=1239241#post1239241
https://eileenslounge.com/viewtopic.php?p=274367&sid=6b84ff6917c71e849aaeaa2 81d06fc31#p27436
https://eileenslounge.com/viewtopic.php?f=30&t=34217&p=265384#p265384
Ref
https://www.excelforum.com/excel-new...ml#post4571172
https://www.excelforum.com/tips-and-...ml#post5408376
https://www.excelforum.com/excel-pro...dim-array.html
http://www.eileenslounge.com/viewtop...271035#p271035
https://www.ozgrid.com/forum/index.p...41#post1239241 , https://eileenslounge.com/viewtopic....d06fc31#p27436
https://eileenslounge.com/viewtopic....265384#p265384
https://eileenslounge.com/viewtopic....266691#p266691

I missed the point, ( possibly ), with the OPs original data, saying he had like this
1,2, 3 , 3 , 3 , 4 , 4 , 4 , 4, 4 , 4 , 4 . 4 , 4 , 4 , 4 , 4
, but wanted this:
1,2, 3 , 4 , 4 . 4 , 4 , 3 , 4 , 4 , 4 , 4 , 4 , 3 , 4 , 4 , 4
I missed the point ( possibly ) that there could be more than one level 2 and that maybe the levels could go on a lot further above level 4. Maybe that additional information is obvious to most people? It is not to me. The more flexible open ended requirement would explain all the recursioning, explorer tree view type things discussed.

Never mind.. , a restricted scenario could still be useful to investigate for another solution.
Restricted solution
Restrictions:
One Big Boss , level 1
, a deputy who does all his work, Level 2
, or rather organises the line managers, level 3
, who in turn have all the workers organised beneath them, level 4

Macro Sub AlanAlmostGotThePointAgain()
Rem 0 I bring the data into an array in one go, to do some things a bit more efficiently, but this solution is still not a reduce the interaction with the workbook to 2 instances: reading data, writing the result
Rem 1 Based on the restrictions, this simply adds the first few lines in the final data for output, in the re ordered column order.

Rem 2a
A basic formula is used in an “Evaluate Range” type VBA code line. It’s based on a basic spreadsheet formula of the type
=IF(C5:C19=$B$4;ROW(B5:B19);0)
In words, what this is doing is:
For the level 2, the one position 2, LR0201 , is searched for in the POSITION REPORTING column C. The result is returned in the form of spreadsheet row number, and form the test data will look like this


5
6
7
0
0
0
0
0
0
0
0
0
0
0
0

In Rem 3 , this information gives us the count of level 3s, Lvl3s , and then in the first bit of Rem 4, Rem4a , we use this information to from the input array, the information at the correct position in the final output data array , ( using the running count position variable, Dw to give the required position in the final output data array )

Rem 4
This section is a typical inner loop within an outer loop type situation. Rem 4a in the outer loop section deals with the level 3 positions in the final output array, - at each of ( three in the sample data, ) level 3s we have a similar “Evaluate Range” formula to that used previously, - in this case, the formula in Rem 4b , based on this sort of spreadsheet formula,
=IF(C8:C19=$B$5;ROW(C8:C19);0)
, is used to give us the row within the input data to find each set of level 4s reporting to any particular level 3.
For example, on the case of the first outer loop, ( CntInds3 = 1 ) we look for a POSITION REPORTING of LA0101 , and obtain a spread of results of the following form from that single line evaluate range type formula


0
0
0
0
0
13
14
15
16
0
0
0

The inner loop of section Rem 4c deals with giving us the data in the output data array for those ) in the example data, 4 for the first outer loop, ) found level 4s reporting to the level 3 being considered in the outer loop.


Here is a full coding with some extra 'comment notes

Code:

---

'
Sub AlanAlmostGotThePointAgain() '        https://eileenslounge.com/viewtopic.php?p=306916&sid=baf68db6f023ebc9d65767c7abf9e19d#p306916
Rem 0 worksheets data info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim arrIn() As Variant: Let arrIn() = Ws1.Range("A1").CurrentRegion.Value2                          '    Ws1.Range("A1").CurrentRegion.Resize(Ws1.Range("A1").CurrentRegion.Rows.Count + 1).Value2
Dim arr1DArrays() As Variant ' https://eileenslounge.com/viewtopic.php?p=306912#p306912  https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)?p=19940&viewfull=1#post19940    https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)/page54#post19940
 ReDim arr1DArrays(1 To UBound(arrIn(), 1)) '                                                                        ReDim arr1DArrays(1 To UBound(arrIn(), 1) - 1)                                                    ' Each element will be a row in the final output - see links in last line
Dim Lr As Long: Let Lr = UBound(arrIn(), 1)
Rem 1 some initial lines in the final output, based on the  Restrictions  of  one Boss and 1 deputy, so in other words one level 1 and one level 2
 Let arr1DArrays(1) = Application.Index(Ws1.Cells, 1, Array(1, 4, 2, 5, 6))    '  Ws1.Range("A1:E1").Value2  '
 Let arr1DArrays(2) = Application.Index(Ws1.Cells, 2, Array(1, 4, 2, 5, 6))
 Let arr1DArrays(3) = Application.Index(Ws1.Cells, 3, Array(1, 4, 2, 5, 6))
 Let arr1DArrays(4) = Application.Index(Ws1.Cells, 4, Array(1, 4, 2, 5, 6))
Rem 2a
Dim Dw As Long: Let Dw = 4 ' The main data row for output.  Dw is like a running count keeping note of the next line to add output data to
                                                                                                                    'Dim Lvl As Long: Let Lvl = 2
Dim srchVl As String: Let srchVl = arrIn(Dw, 2)
Dim arrInds3() As Variant: Let arrInds3() = Ws1.Evaluate("=IF(C5:C" & Lr & "=$B$4,ROW(B5:B" & Lr & "),0)")
Rem 3b
Dim Inds3 As Long
    For Inds3 = 1 To UBound(arrInds3(), 1)
    If arrInds3(Inds3, 1) = 0 Then: Dim Lvl3s As Long: Let Lvl3s = Inds3 - 1: Exit For
                                                                                                                                '    If arrInds3(Inds3, 1) = 0 Then Let Dw = Dw + Inds3 + 2: Dim Lvl3s As Long: Let Lvl3s = Inds3 - 1: Exit For
                                                                                                                                ' Let arr1DArrays(arrInds3(Inds3, 1) - 2) = Application.Index(Ws1.Cells, arrInds3(Inds3, 1), Array(1, 4, 2, 5, 6))
    Next Inds3
Rem 4
Rem 4a
'  now we want to investigate all the level 4s reporting to all the level 3s
Dim CntInds3 As Long ' Looping all level 3s
    For CntInds3 = 1 To Lvl3s ' Looping all level 3s
    Let Dw = Dw + 1
    Let arr1DArrays(Dw) = Application.Index(Ws1.Cells, 5 + CntInds3 - 1, Array(1, 4, 2, 5, 6))
    Rem 4b
    Dim arrInds4() As Variant: Let arrInds4() = Ws1.Evaluate("=IF(C" & 5 + Lvl3s & ":C" & Lr & "=$B$" & 5 + CntInds3 - 1 & ",ROW(C" & 5 + Lvl3s & ":C" & Lr & "),0)")
    Rem 4c
    Dim CntInds4s As Long
        For CntInds4s = 1 To UBound(arrInds4(), 1)
            If arrInds4(CntInds4s, 1) = 0 Then
           
            Else
            Let Dw = Dw + 1 '
            Let arr1DArrays(Dw) = Application.Index(Ws1.Cells, arrInds4(CntInds4s, 1), Array(1, 4, 2, 5, 6))
            End If
        Next CntInds4s
    Next CntInds3

Rem 5 Output - convert the 1D array of 1D array output rows to a 2D range form
 Let Range("AE1").Resize(Lr, 5).Value2 = Application.Index(arr1DArrays(), Evaluate("=ROW(1:" & Lr & ")"), Evaluate("=COLUMN(A:E)"))
End Sub


---

---

Final results and simplified coding in next posts

Code:

---

Sub AlanReporting() '    https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)?p=19941&viewfull=1#post19941
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim arrIn() As Variant: Let arrIn() = Ws1.Range("A1").CurrentRegion.Value2
Dim arr1DArrays() As Variant: ReDim arr1DArrays(1 To UBound(arrIn(), 1)) '
Dim Lr As Long: Let Lr = UBound(arrIn(), 1)
Rem 1 some initial lines in the final output, based on the  Restrictions  of  one Boss and 1 deputy, so in other words one level 1 and one level 2
 Let arr1DArrays(1) = Application.Index(Ws1.Cells, 1, Array(1, 4, 2, 5, 6)): arr1DArrays(2) = Application.Index(Ws1.Cells, 2, Array(1, 4, 2, 5, 6)): arr1DArrays(3) = Application.Index(Ws1.Cells, 3, Array(1, 4, 2, 5, 6)): arr1DArrays(4) = Application.Index(Ws1.Cells, 4, Array(1, 4, 2, 5, 6))
Rem 2a
Dim Dw As Long: Let Dw = 4
Dim srchVl As String: Let srchVl = arrIn(Dw, 2)
Dim arrInds3() As Variant: Let arrInds3() = Ws1.Evaluate("=IF(C5:C" & Lr & "=$B$4,ROW(B5:B" & Lr & "),0)")
Rem 3b
Dim Inds3 As Long
    For Inds3 = 1 To UBound(arrInds3(), 1)
    If arrInds3(Inds3, 1) = 0 Then: Dim Lvl3s As Long: Let Lvl3s = Inds3 - 1: Exit For
    Next Inds3
Rem 4a
'  now we want to investigate all the level 4s reporting to all the level 3s
Dim CntInds3 As Long ' Outer loop, Looping all level 3s ' ===================================================
    For CntInds3 = 1 To Lvl3s ' Looping all level 3s
    Let Dw = Dw + 1
    Let arr1DArrays(Dw) = Application.Index(Ws1.Cells, 5 + CntInds3 - 1, Array(1, 4, 2, 5, 6))
    Rem 4b
    Dim arrInds4() As Variant: Let arrInds4() = Ws1.Evaluate("=IF(C" & 5 + Lvl3s & ":C" & Lr & "=$B$" & 5 + CntInds3 - 1 & ",ROW(C" & 5 + Lvl3s & ":C" & Lr & "),0)")
    Rem 4c
    Dim CntInds4s As Long ' Inner loop, Looping all level 4s for a level 3 ' --------------------------------
        For CntInds4s = 1 To UBound(arrInds4(), 1)
            If arrInds4(CntInds4s, 1) = 0 Then
           
            Else
            Let Dw = Dw + 1 '
            Let arr1DArrays(Dw) = Application.Index(Ws1.Cells, arrInds4(CntInds4s, 1), Array(1, 4, 2, 5, 6))
            End If
        Next CntInds4s ' ------------------------------------------------------------------------------------
    Next CntInds3 ' =========================================================================================
Rem 5 Output - convert the 1D array of 1D array output rows to a 2D range form
 Let Range("AE1").Resize(Lr, 5).Value2 = Application.Index(arr1DArrays(), Evaluate("=ROW(1:" & Lr & ")"), Evaluate("=COLUMN(A:E)"))
End Sub

---

_____ Workbook: report.xls ( Using Excel 2007 32 bit )

Row\Col	AE	AF	AG	AH	AI
1	DATA
2	DIVISION	LEVEL_NO	POSITION	empno	code
3	XX	1	OZ00301	E1	LL81
4	XX	2	LR0201	E2	LL82
5	XX	3	LA0101	E3	LL83
6	XX	4	XX0101	E11	LL91
7	XX	4	XX0102	E12	LL92
8	XX	4	XX0103	E13	LL93
9	XX	4	XX0104	E14	LL94
10	XX	3	LA0201	E4	LL84
11	XX	4	XX0502	E6	LL86
12	XX	4	XX0601	E7	LL87
13	XX	4	XX1901	E8	LL88
14	XX	4	XX2101	E9	LL89
15	XX	4	XX0501	E17	LL97
16	XX	3	LA0701	E5	LL85
17	XX	4	XX2201	E10	LL90
18	XX	4	XX0201	E15	LL95
19	XX	4	XX0301	E16	LL96

Worksheet: Sheet1

We note a slight difference in order presented in the final results for the level 4s,
https://excelfox.com/forum/showthrea...ll=1#post19943
https://bit.ly/3LpFarN
, when compared with results from the other solutions so far given
https://excelfox.com/forum/showthrea...ll=1#post19944
https://bit.ly/3L5bLBV

- This sort of difference is commonly seen when comparing explorer / recursioning type solutions with simpler looping ones which build up the results one line after the other - recursioning type solutions go up and down the explorer tree view structure thingy and so often order the final results a bit differently.






_____ Workbook: report.xls ( Using Excel 2007 32 bit )

Row\Col	I	J	K	L	M	AE	AF	AG	AH	AI
1	REQUIRE					DATA
2	DIVISION	LEVEL_NO	POSITION	empno	code	DIVISION	LEVEL_NO	POSITION	empno	code
3	XX	1	OZ00301	E1	LL81	XX	1	OZ00301	E1	LL81
4	XX	2	LR0201	E2	LL82	XX	2	LR0201	E2	LL82
5	XX	3	LA0101	E3	LL83	XX	3	LA0101	E3	LL83
6	XX	4	XX0101	E11	LL91	XX	4	XX0101	E11	LL91
7	XX	4	XX0102	E12	LL92	XX	4	XX0102	E12	LL92
8	XX	4	XX0103	E13	LL93	XX	4	XX0103	E13	LL93
9	XX	4	XX0104	E14	LL94	XX	4	XX0104	E14	LL94
10	XX	3	LA0201	E4	LL84	XX	3	LA0201	E4	LL84
11	XX	4	XX0501	E17	LL97	XX	4	XX0502	E6	LL86
12	XX	4	XX0502	E6	LL86	XX	4	XX0601	E7	LL87
13	XX	4	XX0601	E7	LL87	XX	4	XX1901	E8	LL88
14	XX	4	XX1901	E8	LL88	XX	4	XX2101	E9	LL89
15	XX	4	XX2101	E9	LL89	XX	4	XX0501	E17	LL97
16	XX	3	LA0701	E5	LL85	XX	3	LA0701	E5	LL85
17	XX	4	XX0201	E15	LL95	XX	4	XX2201	E10	LL90
18	XX	4	XX0301	E16	LL96	XX	4	XX0201	E15	LL95
19	XX	4	XX2201	E10	LL90	XX	4	XX0301	E16	LL96

Worksheet: Sheet1