VBA function to shift columns by variable value starting with variable column number

[kt_mr_excel]

Help! I have a very large volume of spreadsheets that were converted from PDF files and need to correct some column shifts that resulted from the conversion. The worksheets have thousands of rows that would need all cells to be shifted to the right from a variable starting column and variable amount by row. I have two columns, A & B, where A displays the letter value of the starting column to shift (like "L") and B contains a numeric value indicating the amount to shift. So ideally, I need to start on row 2 (to avoid the header row) and loop through all rows that contain data (rather than a fixed number of rows) and perform the appropriate shift for each row based on the values in columns A & B. I've found some similar logic in the forum but nothing that handles all of the variables. Any help will be be GREATLY appreciated!

 

[kt_mr_excel]

*cmowla, I hate to ask, but my next step is to see how I could run your function against all workbooks within a folder, which would not contain the A & B columns like the previous example, so would therefore have to calculate where the offset should begin for each row and also the amount of the offset. The offset logic is as follows:
The starting point for the offset of each row is determined by fetching the column number where a particular text string is found and then adding a positive or negative adjustment value to that column number. For example if "ABCD" is located in Column 11 and the specified adjustment value is -3, then the starting column for the shift on that row would be Column 8. The amount of the offset needed is then determined by finding the highest Column number where the specified text is found throughout the entire record set and then subtracting the Column number where it is found on the current row. So if the highest column number containing the text string were Column 11, and on the current row it is found in Column 7, the required offset amount would be 4. Likewise, any rows where the column match number is already equal to the max column number would have an offset of 0 since it is already in the proper location. There will be about 30 different combinations of text strings and adjustment values depending on the originating vendor of the file. I assume these could be passed as variables, but may actually be easier for me to just save different versions of the code that reflect the name and specific values of each vendor. When the function is run against a particular folder, all of the workbooks will be from the same vendor and therefore use the same text string and adjustment value criteria.

 

[cmowla]

Out of curiosity, I removed the unnecessary filters (based on what you said eariler) and comments (I have the original code and a video to explain everything, anyway). This is the code that's left.

It ran the example I did a time comparison of early in 0.02 seconds. (4 times faster) I can try to shorten the code more if applicable to your new request.

Sub Test__Shift_Rows_In_The_Workbook_In_The_Sheet()
Call Shift_Rows_In_The_Workbook_In_The_Sheet(ActiveWorkbook, ActiveSheet.Name, 2)
End Sub
Sub Shift_Rows_In_The_Workbook_In_The_Sheet(book As Workbook, sheetName As String, startRow As Long)
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
With book.Sheets(sheetName)
    Dim amountToShift As Integer, currentBlockLengthToShift As Integer, currentStartColumnNumber As Integer, amountToShift_Input$
    Dim currentBlockStartingLocation As Range, currentBlockDestination As Range, currentBlockToDeleteValuesAfterTheMove As Range, cell As Range
    For Each cell In .Range("A" & startRow & ":" & "A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        amountToShift_Input = Trim(cell.Offset(0, 1).Value)
        If (Len(Trim(cell.Value)) = 0) Or (Len(amountToShift_Input) = 0) Then GoTo Next_Row
        amountToShift = CInt(amountToShift_Input)
        currentStartColumnNumber = Columns(Trim(cell.Value)).Column
        currentBlockLengthToShift = .Cells(cell.Row, 16384).End(xlToLeft).Column - currentStartColumnNumber + 1
        Set currentBlockStartingLocation = .Range(.Cells(cell.Row, currentStartColumnNumber), .Cells(cell.Row, currentStartColumnNumber + currentBlockLengthToShift - 1))
        Set currentBlockDestination = .Range(.Cells(cell.Row, currentStartColumnNumber + amountToShift), .Cells(cell.Row, currentStartColumnNumber + currentBlockLengthToShift - 1 + amountToShift))
        Set currentBlockToDeleteValuesAfterTheMove = .Range(.Cells(cell.Row, currentStartColumnNumber), .Cells(cell.Row, currentStartColumnNumber + amountToShift - 1))
        currentBlockDestination.Value = currentBlockStartingLocation.Value
        currentBlockToDeleteValuesAfterTheMove.Value = ""
Next_Row:
    Next cell
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

*cmowla, I hate to ask, but my next step is to see how I could run your function against all workbooks within a folder,

That can easily be done. As long as all workbooks in the file need to be processed (or have some common text in their file name).

which would not contain the A & B columns like the previous example, so would therefore have to calculate where the offset should begin for each row and also the amount of the offset.

I was actually curious as to how you calculated the offsets, so this sounds interesting!

The offset logic is as follows:
The starting point for the offset of each row is determined by fetching the column number where a particular text string is found and then adding a positive or negative adjustment value to that column number. For example if "ABCD" is located in Column 11 and the specified adjustment value is -3, then the starting column for the shift on that row would be Column 8.

How do you get the adjustment values? Is that the same as the "required offset"?

The amount of the offset needed is then determined by finding the highest Column number where the specified text is found throughout the entire record set and then subtracting the Column number where it is found on the current row. So if the highest column number containing the text string were Column 11, and on the current row it is found in Column 7, the required offset amount would be 4. Likewise, any rows where the column match number is already equal to the max column number would have an offset of 0 since it is already in the proper location.

All of this makes perfect sense. (You explain things very well!)

There will be about 30 different combinations of text strings and adjustment values depending on the originating vendor of the file. I assume these could be passed as variables, but may actually be easier for me to just save different versions of the code that reflect the name and specific values of each vendor. When the function is run against a particular folder, all of the workbooks will be from the same vendor and therefore use the same text string and adjustment value criteria.

If these 30 or so specified combinations of text strings for each vendor are going to remain constant for some time, but their Excel Workbooks change often, you can simply choose to create a .txt file for each vendor. In each, only have the 30 or so text strings.. The program can then be told to read from those text files. (And you can put them in the same folder as the Excel Workbooks.)

Am I understanding you correctly about everything?

EDIT:
Question: Are all the file extensions of the Excel Workbooks the same? (Are all .xlsx?)

 

[cmowla]

For example if "ABCD" is located in Column 11 and the specified adjustment value is -3, then the starting column for the shift on that row would be Column 8.

Additional question: When you say "located in Column 11", is that the largest column number where "ABCD" occurs in this row, and 11 + 3 = 14 is the largest column number where "ABCD" occurs in the entire record set?

The starting point for the offset of each row is determined by fetching the column number where a particular text string is found and then adding a positive or negative adjustment value to that column number.

Additional questions: You mentioned that there are 30 or so text strings. If we are looking one row at a time like this, which of the text strings gets priority for an arbitrary row? How does it work if a row contains 2 or more different text strings? Or does it only happen that a row will be assigned on of these 30 text values?

If it happens that multiple text strings can occur in an arbitrary row, would you then choose the largest of the Start Column Number calculated for each and every row (from all Text String) tables. (The result of the comparison of the two Text Strings in the table is in the purple cells of Column G.)

I put together some Excel formulas to do some of these calculations (just to brain storm), and it reflects the above assumptions. Is it correct? (It shows the full record set, but only 2/30 Text string calculations.

Shift Rows Program.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Column #'s Containing the Text String in this row	Largest Column # containing the text string in this row = Largest in [Column A]	Largest column # that the current Text String that appears in the entire record set	Difference between [Column A]'s #'s and the number in [Column C]	Start column number for this row = Largest number in [Column D]	Text String	Largest start columns for all Text Strings = DESIRED start column Numbers for these Rows	Col 8	Col 9	Col 10	Col 11	Col 12	Col 13	Col 14	Col 15	Col 16	Col 17
2			17			ABCD		Records
3	10,11,14,	14		7,6,3,	7		8	Hi	A	ABCD	ABCD	Bye	Neat	ABCD	Cool	Time	ABCDF
4	8,9,12,16,	16		9,8,5,1,	9		9	ABCD	ABCD	Hi	A	ABCD	B	Bye	Neat	ABCD	Cool
5	9,13,15,17,	17		8,4,2,0,	8		8	Hi	ABCD	ABCDE	B	A	ABCD	X	ABCD	Cool	ABCD
6			12			A
7	9,	9		8,	8
8	11,	11		6,	6
9	12,	12		5,	5
Sheet10

Cell Formulas
Range	Formula
H1:Q1	H1	=CONCATENATE("Col ",COLUMN(I1)-1)
C2,C6	C2	=MAX(B3:B5)
A3:A5	A3	=CONCAT(IF(H3:Q3=$F$2,COLUMN(H3:Q3)&",",""))
B3:B5,B7:B9	B3	=VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A3,","," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(A3,","," "))))),LEN(TRIM(SUBSTITUTE(A3,","," "))))))
D3:D5	D3	=CONCAT(IF(H3:Q3=$F$2,$C$2-COLUMN(H3:Q3)&",",""))
E3:E5	E3	=VALUE(MID(D3,1,SEARCH(",",D3,1)-1))
G3:G5	G3	=MAX(E3,E7)
A7:A9	A7	=CONCAT(IF(H3:Q3=$F$6,COLUMN(H3:Q3)&",",""))
D7:D9	D7	=CONCAT(IF(H3:Q3=$F$6,$C$2-COLUMN(H3:Q3)&",",""))
E7:E9	E7	=VALUE(MID(D7,1,SEARCH(",",D3,1)-1))

 

[cmowla]

It looks like I messed up a formula in the sheet. I'll try it again!

[RANGE=rs:9|cs:17|w:Shift Rows Program.xlsb|cls:xl2bb-200|s:Sheet10|tw:724][XR][XH][/XH][XH=w:55]A[/XH][XH=w:43]B[/XH][XH=w:65]C[/XH][XH=w:75]D[/XH][XH=w:62]E[/XH][XH=w:42]F[/XH][XH=w:64]G[/XH][XH=w:31]H[/XH][XH=w:31]I[/XH][XH=w:36]J[/XH][XH=w:31]K[/XH][XH=w:31]L[/XH][XH=w:31]M[/XH][XH=w:31]N[/XH][XH=w:31]O[/XH][XH=w:31]P[/XH][XH=w:36]Q[/XH][/XR][XR][XH]1[/XH][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|ch:66|fz:8pt|cls:ww]Column #'s Containing the Text String in this row[/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:ww]Largest Column # containing the text string in this row = Largest in [Column A][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:bb ww]Largest column # that the current Text String that appears in the entire record set[/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:ww]Difference between [Column A]'s #'s and the number in [Column C][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:ww]Start column number for this row
= Largest number in [Column D][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt]Text String[/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:ww]Largest start columns for all Text Strings
= DESIRED start column Numbers for these Rows[/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:fx][FORMULA==CONCATENATE("Col ",COLUMN(I1)-1)]Col 8[/FORMULA][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:fx][FORMULA==CONCATENATE("Col ",COLUMN(J1)-1)]Col 9[/FORMULA][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:fx][FORMULA==CONCATENATE("Col ",COLUMN(K1)-1)]Col 10[/FORMULA][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:fx][FORMULA==CONCATENATE("Col ",COLUMN(L1)-1)]Col 11[/FORMULA][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:fx][FORMULA==CONCATENATE("Col ",COLUMN(M1)-1)]Col 12[/FORMULA][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:fx][FORMULA==CONCATENATE("Col ",COLUMN(N1)-1)]Col 13[/FORMULA][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:fx][FORMULA==CONCATENATE("Col ",COLUMN(O1)-1)]Col 14[/FORMULA][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:fx][FORMULA==CONCATENATE("Col ",COLUMN(P1)-1)]Col 15[/FORMULA][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:fx][FORMULA==CONCATENATE("Col ",COLUMN(Q1)-1)]Col 16[/FORMULA][/XD][XD=h:c|v:m|fw:b|bc:595959|c:FFFFFF|fz:8pt|cls:fx][FORMULA==CONCATENATE("Col ",COLUMN(R1)-1)]Col 17[/FORMULA][/XD][/XR][XR][XH]2[/XH][XD=bc:BDD7EE|ch:16.5][/XD][XD=bc:BDD7EE|cls:br][/XD][XD=h:c|v:m|fw:b|bc:BDD7EE|c:FF0000|fz:8pt|cls:fx bl bt br bb][FORMULA==MAX(B3:B5)]17[/FORMULA][/XD][XD=bc:BDD7EE|cls:bl][/XD][XD=bc:BDD7EE|cls:bb][/XD][XD=h:c|v:m|bc:9BC2E6]ABCD[/XD][XD=bc:9BC2E6][/XD][XD=cs:10|h:c|v:m|fw:b|bc:D9D9D9]Records[/XD][/XR][XR][XH]3[/XH][XD=h:c|v:m|ch:15.75|cls:fx][FORMULA==CONCAT(IF(H3:Q3=$F$2,COLUMN(H3:Q3)&",",""))]10,11,14,[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A3,","," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(A3,","," "))))),LEN(TRIM(SUBSTITUTE(A3,","," "))))))]14[/FORMULA][/XD][XD=cls:bt][/XD][XD=h:c|cls:fx br][FORMULA==CONCAT(IF(H3:Q3=$F$2,$C$2-COLUMN(H3:Q3)&",",""))]7,6,3,[/FORMULA][/XD][XD=h:c|bc:FFFF00|cls:fx bl bt br][FORMULA==VALUE(MID(D3,1,SEARCH(",",D3,1)-1))]7[/FORMULA][/XD][XD=cls:bl][/XD][XD=h:c|bc:7030A0|c:FFFFFF|cls:fx][FORMULA==MAX(E3,E7)]7[/FORMULA][/XD][XD=h:c|v:m|bc:D9D9D9]Hi[/XD][XD=h:c|v:m|bc:D9D9D9]A[/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][XD=h:c|v:m|bc:D9D9D9]Bye[/XD][XD=h:c|v:m|bc:D9D9D9]Neat[/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][XD=h:c|v:m|bc:D9D9D9]Cool[/XD][XD=h:c|v:m|bc:D9D9D9]Time[/XD][XD=h:c|v:m|bc:D9D9D9]ABCDF[/XD][/XR][XR][XH]4[/XH][XD=h:c|v:m|ch:15|cls:fx][FORMULA==CONCAT(IF(H4:Q4=$F$2,COLUMN(H4:Q4)&",",""))]8,9,12,16,[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A4,","," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(A4,","," "))))),LEN(TRIM(SUBSTITUTE(A4,","," "))))))]16[/FORMULA][/XD][XD][/XD][XD=h:c|cls:fx br][FORMULA==CONCAT(IF(H4:Q4=$F$2,$C$2-COLUMN(H4:Q4)&",",""))]9,8,5,1,[/FORMULA][/XD][XD=h:c|bc:FFFF00|cls:fx bl br][FORMULA==VALUE(MID(D4,1,SEARCH(",",D4,1)-1))]9[/FORMULA][/XD][XD=cls:bl][/XD][XD=h:c|v:m|bc:7030A0|c:FFFFFF|cls:fx][FORMULA==MAX(E4,E8)]9[/FORMULA][/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][XD=h:c|v:m|bc:D9D9D9]Hi[/XD][XD=h:c|v:m|bc:D9D9D9]A[/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][XD=h:c|v:m|bc:D9D9D9]B[/XD][XD=h:c|v:m|bc:D9D9D9]Bye[/XD][XD=h:c|v:m|bc:D9D9D9]Neat[/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][XD=h:c|v:m|bc:D9D9D9]Cool[/XD][/XR][XR][XH]5[/XH][XD=h:c|v:m|ch:15.75|cls:fx][FORMULA==CONCAT(IF(H5:Q5=$F$2,COLUMN(H5:Q5)&",",""))]9,13,15,17,[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A5,","," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(A5,","," "))))),LEN(TRIM(SUBSTITUTE(A5,","," "))))))]17[/FORMULA][/XD][XD=cls:bb][/XD][XD=h:c|cls:fx br][FORMULA==CONCAT(IF(H5:Q5=$F$2,$C$2-COLUMN(H5:Q5)&",",""))]8,4,2,0,[/FORMULA][/XD][XD=h:c|bc:FFFF00|cls:fx bl br bb][FORMULA==VALUE(MID(D5,1,SEARCH(",",D5,1)-1))]8[/FORMULA][/XD][XD=cls:bl][/XD][XD=h:c|v:m|bc:7030A0|c:FFFFFF|cls:fx][FORMULA==MAX(E5,E9)]8[/FORMULA][/XD][XD=h:c|v:m|bc:D9D9D9]Hi[/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][XD=h:c|v:m|bc:D9D9D9]ABCDE[/XD][XD=h:c|v:m|bc:D9D9D9]B[/XD][XD=h:c|v:m|bc:D9D9D9]A[/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][XD=h:c|v:m|bc:D9D9D9]X[/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][XD=h:c|v:m|bc:D9D9D9]Cool[/XD][XD=h:c|v:m|bc:D9D9D9]ABCD[/XD][/XR][XR][XH]6[/XH][XD=bc:BDD7EE|ch:16.5][/XD][XD=bc:BDD7EE|cls:br][/XD][XD=h:c|v:m|fw:b|bc:BDD7EE|c:FF0000|fz:8pt|cls:fx bl bt br bb][FORMULA==MAX(B7:B9)]12[/FORMULA][/XD][XD=bc:BDD7EE|cls:bl][/XD][XD=bc:BDD7EE|cls:bt bb][/XD][XD=h:c|v:m|bc:9BC2E6]A[/XD][XD=bc:9BC2E6][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]7[/XH][XD=h:c|v:m|ch:15.75|cls:fx][FORMULA==CONCAT(IF(H3:Q3=$F$6,COLUMN(H3:Q3)&",",""))]9,[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A7,","," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(A7,","," "))))),LEN(TRIM(SUBSTITUTE(A7,","," "))))))]9[/FORMULA][/XD][XD=cls:bt][/XD][XD=h:c|cls:fx br][FORMULA==CONCAT(IF(H3:Q3=$F$6,$C$6-COLUMN(H3:Q3)&",",""))]3,[/FORMULA][/XD][XD=h:c|bc:FFFF00|cls:fx bl bt br][FORMULA==VALUE(MID(D7,1,SEARCH(",",D3,1)-1))]3[/FORMULA][/XD][XD=cls:bl][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]8[/XH][XD=h:c|v:m|ch:15|cls:fx][FORMULA==CONCAT(IF(H4:Q4=$F$6,COLUMN(H4:Q4)&",",""))]11,[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A8,","," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(A8,","," "))))),LEN(TRIM(SUBSTITUTE(A8,","," "))))))]11[/FORMULA][/XD][XD][/XD][XD=h:c|cls:fx br][FORMULA==CONCAT(IF(H4:Q4=$F$6,$C$6-COLUMN(H4:Q4)&",",""))]1,[/FORMULA][/XD][XD=h:c|bc:FFFF00|cls:fx bl br][FORMULA==VALUE(MID(D8,1,SEARCH(",",D4,1)-1))]1[/FORMULA][/XD][XD=cls:bl][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]9[/XH][XD=h:c|v:m|ch:15.75|cls:fx][FORMULA==CONCAT(IF(H5:Q5=$F$6,COLUMN(H5:Q5)&",",""))]12,[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A9,","," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(A9,","," "))))),LEN(TRIM(SUBSTITUTE(A9,","," "))))))]12[/FORMULA][/XD][XD][/XD][XD=h:c|cls:fx br][FORMULA==CONCAT(IF(H5:Q5=$F$6,$C$6-COLUMN(H5:Q5)&",",""))]0,[/FORMULA][/XD][XD=h:c|bc:FFFF00|cls:fx bl br bb][FORMULA==VALUE(MID(D9,1,SEARCH(",",D5,1)-1))]0[/FORMULA][/XD][XD=cls:bl][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][/RANGE][RANGE=cls:xl2bb-extra-200|t:cf][XR][XD]H1:Q1[/XD][XD=fw:b]H1[/XD][XD]=CONCATENATE("Col ",COLUMN(I1)-1)[/XD][/XR][XR][XD]C2,C6[/XD][XD=fw:b]C2[/XD][XD]=MAX(B3:B5)[/XD][/XR][XR][XD]A3:A5[/XD][XD=fw:b]A3[/XD][XD]=CONCAT(IF(H3:Q3=$F$2,COLUMN(H3:Q3)&",",""))[/XD][/XR][XR][XD]B3:B5,B7:B9[/XD][XD=fw:b]B3[/XD][XD]=VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A3,","," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(A3,","," "))))),LEN(TRIM(SUBSTITUTE(A3,","," "))))))[/XD][/XR][XR][XD]D3:D5[/XD][XD=fw:b]D3[/XD][XD]=CONCAT(IF(H3:Q3=$F$2,$C$2-COLUMN(H3:Q3)&",",""))[/XD][/XR][XR][XD]E3:E5[/XD][XD=fw:b]E3[/XD][XD]=VALUE(MID(D3,1,SEARCH(",",D3,1)-1))[/XD][/XR][XR][XD]G3:G5[/XD][XD=fw:b]G3[/XD][XD]=MAX(E3,E7)[/XD][/XR][XR][XD]A7:A9[/XD][XD=fw:b]A7[/XD][XD]=CONCAT(IF(H3:Q3=$F$6,COLUMN(H3:Q3)&",",""))[/XD][/XR][XR][XD]D7:D9[/XD][XD=fw:b]D7[/XD][XD]=CONCAT(IF(H3:Q3=$F$6,$C$6-COLUMN(H3:Q3)&",",""))[/XD][/XR][XR][XD]E7:E9[/XD][XD=fw:b]E7[/XD][XD]=VALUE(MID(D7,1,SEARCH(",",D3,1)-1))[/XD][/XR][/RANGE]

 

[cmowla]

It looks like I messed up a formula in the sheet. Here's the updated one.

Shift Rows Program.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Column #'s Containing the Text String in this row	Largest Column # containing the text string in this row = Largest in [Column A]	Largest column # that the current Text String appears in the entire record set	Difference between [Column A]'s #'s and the number in [Column C]	Start column number for this row = Largest number in [Column D]	Text String	Largest start columns for all Text Strings = DESIRED start column Numbers for these Rows	Col 8	Col 9	Col 10	Col 11	Col 12	Col 13	Col 14	Col 15	Col 16	Col 17
2			17			ABCD		Records
3	10,11,14,	14		7,6,3,	7		7	Hi	A	ABCD	ABCD	Bye	Neat	ABCD	Cool	Time	ABCDF
4	8,9,12,16,	16		9,8,5,1,	9		9	ABCD	ABCD	Hi	A	ABCD	B	Bye	Neat	ABCD	Cool
5	9,13,15,17,	17		8,4,2,0,	8		8	Hi	ABCD	ABCDE	B	A	ABCD	X	ABCD	Cool	ABCD
6			12			A
7	9,	9		3,	3
8	11,	11		1,	1
9	12,	12		0,	0
Sheet10

Cell Formulas
Range	Formula
H1:Q1	H1	=CONCATENATE("Col ",COLUMN(I1)-1)
C2,C6	C2	=MAX(B3:B5)
A3:A5	A3	=CONCAT(IF(H3:Q3=$F$2,COLUMN(H3:Q3)&",",""))
B3:B5,B7:B9	B3	=VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A3,","," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(A3,","," "))))),LEN(TRIM(SUBSTITUTE(A3,","," "))))))
D3:D5	D3	=CONCAT(IF(H3:Q3=$F$2,$C$2-COLUMN(H3:Q3)&",",""))
E3:E5	E3	=VALUE(MID(D3,1,SEARCH(",",D3,1)-1))
G3:G5	G3	=MAX(E3,E7)
A7:A9	A7	=CONCAT(IF(H3:Q3=$F$6,COLUMN(H3:Q3)&",",""))
D7:D9	D7	=CONCAT(IF(H3:Q3=$F$6,$C$6-COLUMN(H3:Q3)&",",""))
E7:E9	E7	=VALUE(MID(D7,1,SEARCH(",",D3,1)-1))

 

[kt_mr_excel]

Answers to your questions:
1.) How do you get the adjustment values? Is that the same as the "required offset"?
No, the unique text string is the only reliable way tell if a column shift has occurred on a specific row but usually not the starting point of the offset correction. The "adjustment value" is a fixed number of columns (positive, negative, or zero) that when added to the column number of the located text string on each row will indicate the appropriate starting point for the offset on that row. The "required offset" amount I described earlier is calculated by first locating the text string that is furthest to the right in the table (Max Column) and then calculating the offset required on each row to align the data. (Max column number minus the column number where text is located on each row.) Please note also that the text string will never appear more than once per row (looks like you are allowing for multiples) and also will not appear on every row so those rows can be ignored.
2.) Are all the file extensions of the Excel Workbooks the same? (Are all .xlsx?)
Yes - I'm still working on an approach to batch convert a huge number of PDF files, but the conversion tools I've been testing only seem to do .xlsx conversion. If you have any opinions on a suitable conversion tool, I'd appreciate any suggestions!
3.) When you say "located in Column 11", is that the largest column number where "ABCD" occurs in this row, and 11 + 3 = 14 is the largest column number where "ABCD" occurs in the entire record set?
To clarify, for a particular vendor which has a text string value of "ABCD" and an adjustment value of -3, that would mean that the starting point of the offset will be 3 columns before the text string on each row and a positive 3 adjustment value would make the starting point 3 columns after the text string (likewise, a zero adjustment value would start at the text string column). So knowing where to begin the offset for each row, the offset amount needs to be calculated for each row by subtracting the column number of the text string on each row from the Max column number of the text string found in the table (the location that is furthest to the right).

I hope I answered all of your questions - please let me know if something needs further clarification.

 

[cmowla]

I hope I answered all of your questions - please let me know if something needs further clarification.

Does the following "image" represent what you want to do?

• The final calculated offsets (which were the Column B values that I had as input for my previous code) are in the red column.
• The "record set" is in range A2:J17. I constructed it so that only one occurrence of text strings A, B, C, or D is on each row.
• The numbers 1-10 in range A1:J1 are just the indices of the columns in the "record set".
• Any number other than zero in the blue row of numbers (A19:J19) represents that the text string A occurs in the column above that number. (And the same for the remaining 3 colored rows for text strings B-D.) So treat any non-zero number just as a marker.
• The numbers in range K19:K22 are the largest column numbers that their corresponding (color-coded) Text Strings A-D occur in the "record set".
• The four colored Col Ocr. (column occurrence = where the numbers in these columns actually means something. The column number that their corresponding colored text string occur in the specific row in the table that each of those numbers is in. I am guessing that these correspond to the Column A values that I had as input for my previous code.
• The numbers in the four Offset columns are the same that's in the red OFFSETcolumn. (I just used sum to act as a cheap form of a filter!
  • The formula to calculate the (final) offset is [Largest]-([Col Ocr.]+[Adjustment Val]).
  • It's interesting, because I had to play with the adjustment values a little to make the Offsets (all numbers in the four colored columns that are not red) to be >=0.
  • It kind of reminds me of my cryptography course I took in college, LOL.

New Microsoft Excel Worksheet (2).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	1	2	3	4	5	6	7	8	9	10	Col. Ocr	Col. Ocr	Col. Ocr	Col. Ocr	Offset	Offset	Offset	Offset	OFFSET
2	A	text	text	text	text	text	text	text	text	text	1	0	0	0	7	0	0	0	7
3	text	text	text	Text	text	B	text	text	text	text	0	6	0	0	0	4	0	0	4
4	text	text	text	C	text	text	text	text	text	text	0	0	4	0	0	0	4	0	4
5	text	D	text	text	text	text	text	text	text	text	0	0	0	2	0	0	0	7	7
6	text	text	text	A	text	text	text	text	text	text	4	0	0	0	4	0	0	0	4
7	text	text	C	text	text	text	text	text	text	B	0	10	3	0	0	0	5	0	5
8	text	text	text	text	text	text	D	text	text	text	0	0	0	7	0	0	0	2	2
9	text	text	text	text	B	text	text	text	text	text	0	5	0	0	0	5	0	0	5
10	text	text	text	text	text	text	A	text	text	text	7	0	0	0	1	0	0	0	1
11	text	text	text	A	text	text	text	text	text	text	4	0	0	0	4	0	0	0	4
12	text	text	text	text	D	text	text	text	text	text	0	0	0	5	0	0	0	4	4
13	text	text	B	text	text	text	text	text	text	text	0	3	0	0	0	7	0	0	7
14	text	text	text	text	text	D	text	text	text	text	0	0	0	6	0	0	0	3	3
15	text	A	text	text	text	text	text	text	text	text	2	0	0	0	6	0	0	0	6
16	C	text	text	text	text	text	text	text	text	text	0	0	1	0	0	0	7	0	7
17	text	text	text	text	text	text	text	A	text	text	8	0	0	0	0	0	0	0	0
18											Largest								Adjustment Vals	Text String
19	1	14	0	5	0	0	9	16	0	0	8								0	A
20	0	0	12	0	8	2	0	0	0	6	10								0	B
21	15	0	6	3	0	0	0	0	0	0	4								-4	C
22	0	4	0	0	11	13	7	0	0	0	7								-2	D
Sheet1 (4)

Cell Formulas
Range	Formula
K2:K17	K2	=IFNA(MATCH($T$19,A2:J2,0),0)
L2:L17	L2	=IFNA(MATCH($T$20,A2:J2,0),0)
M2:M17	M2	=IFNA(MATCH($T$21,A2:J2,0),0)
N2:N17	N2	=IFNA(MATCH($T$22,A2:J2,0),0)
O2:O17	O2	=IF(K2>0,$K$19-(K2+$S$19),0)
P2:P17	P2	=IF(L2>0,$K$20-(L2+$S$20),0)
Q2:Q17	Q2	=IF(M2>0,$K$21-(M2+$S$21),0)
R2:R17	R2	=IF(N2>0,$K$22-(N2+$S$22),0)
S2:S17	S2	=SUM(O2:R2)
A19:A22	A19	=IFNA(MATCH(T19,$A$2:$A$17,0),0)
B19:B22	B19	=IFNA(MATCH(T19,$B$2:$B$17,0),0)
C19:C22	C19	=IFNA(MATCH(T19,$C$2:$C$17,0),0)
D19:D22	D19	=IFNA(MATCH(T19,$D$2:$D$17,0),0)
E19:E22	E19	=IFNA(MATCH(T19,$E$2:$E$17,0),0)
F19:F22	F19	=IFNA(MATCH(T19,$F$2:$F$17,0),0)
G19:G22	G19	=IFNA(MATCH(T19,$G$2:$G$17,0),0)
H19:H22	H19	=IFNA(MATCH(T19,$H$2:$H$17,0),0)
I19:I22	I19	=IFNA(MATCH(T19,$I$2:$I$17,0),0)
J19:J22	J19	=IFNA(MATCH(T19,$J$2:$J$17,0),0)
K19:K22	K19	=MAX(IF(A19:J19>0,COLUMN(A19:J19),""))

 

[kt_mr_excel]

Amazing! From what I see in your image, it appears that you are allowing for multiple text strings, which is fantastic flexibility but a degree of complexity higher than I imagined. It looks like there are 2 matching instances on row 7 in your example - just curious if that was intentional and whether 2 separate shifts would result in that instance?

 

[cmowla]

It looks like there are 2 matching instances on row 7 in your example - just curious if that was intentional and whether 2 separate shifts would result in that instance?

Oh, man! It was a mistake actually. But changing it didn't make this that much different:

New Microsoft Excel Worksheet (2).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	1	2	3	4	5	6	7	8	9	10	Col. Ocr	Col. Ocr	Col. Ocr	Col. Ocr	Offset	Offset	Offset	Offset	OFFSET
2	A	text	text	text	text	text	text	text	text	text	1	0	0	0	7	0	0	0	7
3	text	text	text	Text	text	B	text	text	text	text	0	6	0	0	0	0	0	0	0
4	text	text	text	C	text	text	text	text	text	text	0	0	4	0	0	0	4	0	4
5	text	D	text	text	text	text	text	text	text	text	0	0	0	2	0	0	0	7	7
6	text	text	text	A	text	text	text	text	text	text	4	0	0	0	4	0	0	0	4
7	text	text	C	text	text	text	text	text	text	text	0	0	3	0	0	0	5	0	5
8	text	text	text	text	text	text	D	text	text	text	0	0	0	7	0	0	0	2	2
9	text	text	text	text	B	text	text	text	text	text	0	5	0	0	0	1	0	0	1
10	text	text	text	text	text	text	A	text	text	text	7	0	0	0	1	0	0	0	1
11	text	text	text	A	text	text	text	text	text	text	4	0	0	0	4	0	0	0	4
12	text	text	text	text	D	text	text	text	text	text	0	0	0	5	0	0	0	4	4
13	text	text	B	text	text	text	text	text	text	text	0	3	0	0	0	3	0	0	3
14	text	text	text	text	text	D	text	text	text	text	0	0	0	6	0	0	0	3	3
15	text	A	text	text	text	text	text	text	text	text	2	0	0	0	6	0	0	0	6
16	C	text	text	text	text	text	text	text	text	text	0	0	1	0	0	0	7	0	7
17	text	text	text	text	text	text	text	A	text	text	8	0	0	0	0	0	0	0	0
18											Largest								Adjustment Vals	Text String
19	1	14	0	5	0	0	9	16	0	0	8								0	A
20	0	0	12	0	8	2	0	0	0	0	6								0	B
21	15	0	6	3	0	0	0	0	0	0	4								-4	C
22	0	4	0	0	11	13	7	0	0	0	7								-2	D
Sheet1 (4)

Cell Formulas
Range	Formula
K2:K17	K2	=IFNA(MATCH($T$19,A2:J2,0),0)
L2:L17	L2	=IFNA(MATCH($T$20,A2:J2,0),0)
M2:M17	M2	=IFNA(MATCH($T$21,A2:J2,0),0)
N2:N17	N2	=IFNA(MATCH($T$22,A2:J2,0),0)
O2:O17	O2	=IF(K2>0,$K$19-(K2+$S$19),0)
P2:P17	P2	=IF(L2>0,$K$20-(L2+$S$20),0)
Q2:Q17	Q2	=IF(M2>0,$K$21-(M2+$S$21),0)
R2:R17	R2	=IF(N2>0,$K$22-(N2+$S$22),0)
S2:S17	S2	=SUM(O2:R2)
A19:A22	A19	=IFNA(MATCH(T19,$A$2:$A$17,0),0)
B19:B22	B19	=IFNA(MATCH(T19,$B$2:$B$17,0),0)
C19:C22	C19	=IFNA(MATCH(T19,$C$2:$C$17,0),0)
D19:D22	D19	=IFNA(MATCH(T19,$D$2:$D$17,0),0)
E19:E22	E19	=IFNA(MATCH(T19,$E$2:$E$17,0),0)
F19:F22	F19	=IFNA(MATCH(T19,$F$2:$F$17,0),0)
G19:G22	G19	=IFNA(MATCH(T19,$G$2:$G$17,0),0)
H19:H22	H19	=IFNA(MATCH(T19,$H$2:$H$17,0),0)
I19:I22	I19	=IFNA(MATCH(T19,$I$2:$I$17,0),0)
J19:J22	J19	=IFNA(MATCH(T19,$J$2:$J$17,0),0)
K19:K22	K19	=MAX(IF(A19:J19>0,COLUMN(A19:J19),""))

Amazing! From what I see in your image, it appears that you are allowing for multiple text strings, which is fantastic flexibility but a degree of complexity higher than I imagined.

So it it as you want? If so, you can clearly see that with VBA, we can generate these formulas to surround the "records set" to get the information we need. But is it correct? Do you want me to make a video to explain the formulas to be sure? (And regarding alllowing multiple text strings . . . it is kind of hard to "imagine" what the data is since I've never seen it. This data set was the result of "randomness with constraints to match your criteria".)

 

[kt_mr_excel]

I believe that all of the results are correct. I am currently using a spreadsheet that calculates and performs the offsets (with a much less elegant approach) but it's beyond my comprehension how to accomplish that with VBA and loop through multiple workbooks within the folder. I assume that there needs to be someplace in the code to store the two criteria values (text string to search for and adjustment value to locate the offset starting point)?