Question on using VBA to copy text and paste in appropriate columns

[MiteshG]

Hi,

I have data in text format in an excel sheet (Sheet1) which looks like below.

Country or region: Albania

Last update: 12 May 2023

Status of enactment

No announcement yet

Income Inclusion Rule (IIR)

Entry into force: To be confirmed

No announcement yet

Undertaxed Payments Rule (UTPR)

Entry into force: To be confirmed

No announcement yet

Qualified Domestic Minimum Top-up Tax (QDMTT)

Entry into force: To be confirmed

No announcement yet

Covered Taxes

No information available.

Qualifying Refundable Tax Credits

No information available.

Transitional Safe Harbour

No information available.

Compliance / Filing Requirements

No information available.

I am looking to arrange the data in table format as below in a new sheet

Country or region	Status of enactment	Income Inclusion Rule (IIR)	Undertaxed Payments Rule (UTPR)	Qualified Domestic Minimum Top-up Tax (QDMTT)	Covered Taxes	Qualifying Refundable Tax Credits	Transitional Safe Harbour	Compliance / Filing Requirements
Albania	No announcement yet	Entry into force: To be confirmed	Entry into force: To be confirmed	Entry into force: To be confirmed	No information available.	No information available.	No information available.	No information available.
		No announcement yet	No announcement yet	No announcement yet

How to achieve this with TextToColumns VBA functionality. This same step is to be repeated for 70 countries

 

[RudRud]

Hi, It would be good if you can provide more sample data through mini sheet via XLBB with expected result for better understanding

• Want to help your helpers by posting a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest January 2021 v 2.0 )

Otherwise, you can share the file with us (more sample data) for further clarification by uploading it to Google Drive or Dropbox.

Book3
	A	B
2	Country or region: Albania	1
3	Last update: 12 May 2023	2
4	Status of enactment	1
5	No announcement yet	2
6	Income Inclusion Rule (IIR)	1
7	Entry into force: To be confirmed	2
8	No announcement yet	3
9	Undertaxed Payments Rule (UTPR)	1
10	Entry into force: To be confirmed	2
11	No announcement yet	3
12	Qualified Domestic Minimum Top-up Tax (QDMTT)	1
13	Entry into force: To be confirmed	2
14	No announcement yet	3
15	Covered Taxes	1
16	No information available.	2
17	Qualifying Refundable Tax Credits	1
18	No information available.	2
19	Transitional Safe Harbour	1
20	No information available.	2
21	Compliance / Filing Requirements	1
22	No information available.	2
23
Sheet1

For Income Inlucsion , Qualified Domestic , Undertaxed Payments rule, is it fix 3rows? How can we define either 2rows or 3rows? Can you elaborate more?

 

[MiteshG]

Extract_Specific_Data_from_PDF_to_Excel.xlsm
	A
168	Country or region: Albania
169	Last update: 12 May 2023
170	Status of enactment
171	No announcement yet
172	Income Inclusion Rule (IIR)
173	Entry into force: To be confirmed
174	No announcement yet
175	Undertaxed Payments Rule (UTPR)
176	Entry into force: To be confirmed
177	No announcement yet
178	Qualified Domestic Minimum Top-up Tax (QDMTT)
179	Entry into force: To be confirmed
180	No announcement yet
181	Covered Taxes
182	No information available.
183	Qualifying Refundable Tax Credits
184	No information available.
185	Transitional Safe Harbour
186	No information available.
187	Compliance / Filing Requirements
188	No information available.
189	Country or region: Angola
190	Last update: 9 May 2023
191	Status of enactment
192	No announcement yet
193	Income Inclusion Rule (IIR)
194	Entry into force: To be confirmed
195	No announcement yet
196	Undertaxed Payments Rule (UTPR)
197	Entry into force: To be confirmed
198	No announcement yet
199	Qualified Domestic Minimum Top-up Tax (QDMTT)
200	Entry into force: To be confirmed
201	No announcement yet
202	Covered Taxes
203	No information available.
204	Qualifying Refundable Tax Credits
205	No information available.
206	Transitional Safe Harbour
207	No information available.
208	Compliance / Filing Requirements
209	No information available.
210	Country or region: Argentina
211	Last update: 11 May 2023
212	Status of enactment
213	No announcement yet
214	Income Inclusion Rule (IIR)
215	Entry into force: To be confirmed
216	No announcement yet
217	Undertaxed Payments Rule (UTPR)
218	Entry into force: To be confirmed
219	No announcement yet
220	Qualified Domestic Minimum Top-up Tax (QDMTT)
221	Entry into force: To be confirmed
222	No announcement yet
223	Covered Taxes
224	No information available.
225	Qualifying Refundable Tax Credits
226	No information available.
227	Transitional Safe Harbour
228	No information available
229	Compliance / Filing Requirements
230	No information available.
231	Country or region: Armenia
232	Last update: 15 May 2023
233	Status of enactment
234	No announcement yet
235	Income Inclusion Rule (IIR)
236	Entry into force: To be confirmed
237	No announcement yet
238	Undertaxed Payments Rule (UTPR)
239	Entry into force: To be confirmed
240	No announcement yet
241	Qualified Domestic Minimum Top-up Tax (QDMTT)
242	Entry into force: To be confirmed
243	No announcement yet
244	Covered Taxes
245	No information available.
246	Qualifying Refundable Tax Credits
247	No information available.
248	Transitional Safe Harbour
249	No information available
250	Compliance / Filing Requirements
251	No information available
252	Country or region: Australia
253	Last update: 9 May 2023
254	Status of enactment
255	In the 2023-24 Budget, the Government announced the implementation of a 15% global minimum tax and domestic
256	minimum tax, key aspects of Pillar Two of the OECD/G20 Two-Pillar Solution to address the tax challenges arising
257	from the digitalisation of the economy.
258	The IIR will apply for fiscal years starting on or after 1 January 2024. The UTPR will apply for fiscal years starting on
259	or after 1 January 2025. The domestic minimum tax will apply for income years starting on or after 1 January 2024.
260	The draft legislation has not been released yet.
261	Income Inclusion Rule (IIR)
262	Entry into force: 1 January 2024
263	The IIR will apply to fiscal years that begin on or after 1 January 2024 to:
264	Multinational groups with annual global revenue of EUR750 million or more.
265	In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds,
266	government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti-
267	Base Erosion (GloBE) Rules, and income associated with international shipping
268	Undertaxed Payments Rule (UTPR)
269	Entry into force: 1 January 2025
270	The UTPR will apply to fiscal years that begin on or after 1 January 2025 to:
271	Multinational groups with annual global revenue of EUR750 million or more.
272	In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds,
273	government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti-
274	Base Erosion (GloBE) Rules, and income associated with international shipping
275	Qualified Domestic Minimum Top-up Tax (QDMTT)
276	Entry into force: 1 January 2024
277	The domestic minimum tax will apply to fiscal years that begin on or after 1 January 2024 to:
278	Australian operations of multinational groups with annual global revenue of EUR750 million or more.
279	In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds,
280	government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti-
281	Base Erosion (GloBE) Rules, and income associated with international shipping
282	Covered Taxes
283	No information available.
284	Qualifying Refundable Tax Credits
285	No information available.
286	Transitional Safe Harbour
287	No information available
288	Compliance / Filing Requirements
289	No information available.
290	Federal Budget 2023-24 - Australia will implement key aspects of the OECD's 'Pillar Two'
291	framework, including a domestic minimum tax, with an effective date for some measures from 1
292	January 2024. Read more
Sheet1

 

[MiteshG]

Hi, It would be good if you can provide more sample data through mini sheet via XLBB with expected result for better understanding

• Want to help your helpers by posting a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest January 2021 v 2.0 )

Otherwise, you can share the file with us (more sample data) for further clarification by uploading it to Google Drive or Dropbox.

Book3
	A	B
2	Country or region: Albania	1
3	Last update: 12 May 2023	2
4	Status of enactment	1
5	No announcement yet	2
6	Income Inclusion Rule (IIR)	1
7	Entry into force: To be confirmed	2
8	No announcement yet	3
9	Undertaxed Payments Rule (UTPR)	1
10	Entry into force: To be confirmed	2
11	No announcement yet	3
12	Qualified Domestic Minimum Top-up Tax (QDMTT)	1
13	Entry into force: To be confirmed	2
14	No announcement yet	3
15	Covered Taxes	1
16	No information available.	2
17	Qualifying Refundable Tax Credits	1
18	No information available.	2
19	Transitional Safe Harbour	1
20	No information available.	2
21	Compliance / Filing Requirements	1
22	No information available.	2
23
Sheet1

For Income Inlucsion , Qualified Domestic , Undertaxed Payments rule, is it fix 3rows? How can we define either 2rows or 3rows? Can you elaborate more?

Thank you for your response. I have added the mini sheet. Regarding the columns Income Inclusion, QDMTT, Undertaxed Payment Rule - sometimes the text is in more than 3 rows (Australia for example in the mini sheet)

 

[RudRud]

Book3
	A	B	C	D	E	F	G	H	I
2	Country or region	Status of enactment	Income Inclusion Rule (IIR)	Undertaxed Payments Rule (UTPR)	Qualified Domestic Minimum Top-up Tax (QDMTT)	Covered Taxes	Qualifying Refundable Tax Credits	Transitional Safe Harbour	Compliance / Filing Requirements
3	Albania	No announcement yet	Entry into force: To be confirmed	Entry into force: To be confirmed	Entry into force: To be confirmed	No information available.	No information available.	No information available.	No information available.
4			No announcement yet	No announcement yet	No announcement yet
5	Angola	No announcement yet	Entry into force: To be confirmed	Entry into force: To be confirmed	Entry into force: To be confirmed	No information available.	No information available.	No information available.	No information available.
6			No announcement yet	No announcement yet	No announcement yet
7	Argentina	No announcement yet	Entry into force: To be confirmed	Entry into force: To be confirmed	Entry into force: To be confirmed	No information available.	No information available.	No information available	No information available.
8			No announcement yet	No announcement yet	No announcement yet
9	Armenia	No announcement yet	Entry into force: To be confirmed	Entry into force: To be confirmed	Entry into force: To be confirmed	No information available.	No information available.	No information available	No information available
10			No announcement yet	No announcement yet	No announcement yet
11	Australia	In the 2023-24 Budget, the Government announced the implementation of a 15% global minimum tax and domestic	Entry into force: 1 January 2024	Entry into force: 1 January 2025	Entry into force: 1 January 2024	No information available.	No information available.	No information available	No information available.
12		minimum tax, key aspects of Pillar Two of the OECD/G20 Two-Pillar Solution to address the tax challenges arising	The IIR will apply to fiscal years that begin on or after 1 January 2024 to:	The UTPR will apply to fiscal years that begin on or after 1 January 2025 to:	The domestic minimum tax will apply to fiscal years that begin on or after 1 January 2024 to:				Federal Budget 2023-24 - Australia will implement key aspects of the OECD's 'Pillar Two'
13		from the digitalisation of the economy.	Multinational groups with annual global revenue of EUR750 million or more.	Multinational groups with annual global revenue of EUR750 million or more.	Australian operations of multinational groups with annual global revenue of EUR750 million or more.				framework, including a domestic minimum tax, with an effective date for some measures from 1
14		The IIR will apply for fiscal years starting on or after 1 January 2024. The UTPR will apply for fiscal years starting on	In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds,	In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds,	In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds,				January 2024. Read more
15		or after 1 January 2025. The domestic minimum tax will apply for income years starting on or after 1 January 2024.	government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti-	government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti-	government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti-
16		The draft legislation has not been released yet.	Base Erosion (GloBE) Rules, and income associated with international shipping	Base Erosion (GloBE) Rules, and income associated with international shipping	Base Erosion (GloBE) Rules, and income associated with international shipping
Sheet2

Please put the yellow highlighted columns in Sheet2,

Right click sheet2 -> view code -> paste the code below.

A little bit manual, but you may try first for reference. hopefully there's other references for you

Option Compare Text
Option Explicit
Sub test()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim a As Variant
Dim c%, i%, m%
Dim ss As Range
Dim rec As String
Dim savenum%
Dim dict As Object

Set ws = Sheets("Sheet1")
Set ws2 = Sheets("sheet2")

Set dict = CreateObject("Scripting.Dictionary")
dict.comparemode = vbTextCompare

ReDim b(1 To 5000, 1 To 9)

a = ws.Range("a168:a" & Cells(Rows.Count, "A").End(xlUp).Row).Value 'Start filtering data from A168
c = 1

ws2.[a3:i10000].Clear

For Each ss In ws2.Range("a2:i2")
    dict.Add ss.Value, ss.Column
Next ss

For i = 1 To UBound(a, 1)
    If InStr(a(i, 1), "country") >= 1 Then
         If m > savenum Then
                savenum = m
            End If
        c = 1 + savenum
        b(c, 1) = a(i, 1)
        savenum = 0
    ElseIf dict.exists(a(i - 1, 1)) Then
        b(c, dict(a(i - 1, 1))) = a(i, 1)
            If m > savenum Then
                savenum = m
            End If
        m = c
        rec = a(i - 1, 1)
     
   ElseIf Not InStr(a(i, 1), "last update") >= 1 And Not dict.exists(a(i, 1)) Then
        m = m + 1
       b(m, dict(rec)) = a(i, 1)
       
    End If
    
Next i

Sheets(2).[a3].Resize(UBound(b, 1), UBound(b, 2)).Value = b
Sheet2.Columns("A").Replace what:="Country or region: ", replacement:="", searchorder:=xlByColumns

End Sub

 

[MiteshG]

Book3
	A	B	C	D	E	F	G	H	I
2	Country or region	Status of enactment	Income Inclusion Rule (IIR)	Undertaxed Payments Rule (UTPR)	Qualified Domestic Minimum Top-up Tax (QDMTT)	Covered Taxes	Qualifying Refundable Tax Credits	Transitional Safe Harbour	Compliance / Filing Requirements
3	Albania	No announcement yet	Entry into force: To be confirmed	Entry into force: To be confirmed	Entry into force: To be confirmed	No information available.	No information available.	No information available.	No information available.
4			No announcement yet	No announcement yet	No announcement yet
5	Angola	No announcement yet	Entry into force: To be confirmed	Entry into force: To be confirmed	Entry into force: To be confirmed	No information available.	No information available.	No information available.	No information available.
6			No announcement yet	No announcement yet	No announcement yet
7	Argentina	No announcement yet	Entry into force: To be confirmed	Entry into force: To be confirmed	Entry into force: To be confirmed	No information available.	No information available.	No information available	No information available.
8			No announcement yet	No announcement yet	No announcement yet
9	Armenia	No announcement yet	Entry into force: To be confirmed	Entry into force: To be confirmed	Entry into force: To be confirmed	No information available.	No information available.	No information available	No information available
10			No announcement yet	No announcement yet	No announcement yet
11	Australia	In the 2023-24 Budget, the Government announced the implementation of a 15% global minimum tax and domestic	Entry into force: 1 January 2024	Entry into force: 1 January 2025	Entry into force: 1 January 2024	No information available.	No information available.	No information available	No information available.
12		minimum tax, key aspects of Pillar Two of the OECD/G20 Two-Pillar Solution to address the tax challenges arising	The IIR will apply to fiscal years that begin on or after 1 January 2024 to:	The UTPR will apply to fiscal years that begin on or after 1 January 2025 to:	The domestic minimum tax will apply to fiscal years that begin on or after 1 January 2024 to:				Federal Budget 2023-24 - Australia will implement key aspects of the OECD's 'Pillar Two'
13		from the digitalisation of the economy.	Multinational groups with annual global revenue of EUR750 million or more.	Multinational groups with annual global revenue of EUR750 million or more.	Australian operations of multinational groups with annual global revenue of EUR750 million or more.				framework, including a domestic minimum tax, with an effective date for some measures from 1
14		The IIR will apply for fiscal years starting on or after 1 January 2024. The UTPR will apply for fiscal years starting on	In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds,	In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds,	In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds,				January 2024. Read more
15		or after 1 January 2025. The domestic minimum tax will apply for income years starting on or after 1 January 2024.	government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti-	government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti-	government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti-
16		The draft legislation has not been released yet.	Base Erosion (GloBE) Rules, and income associated with international shipping	Base Erosion (GloBE) Rules, and income associated with international shipping	Base Erosion (GloBE) Rules, and income associated with international shipping
Sheet2

Please put the yellow highlighted columns in Sheet2,

Right click sheet2 -> view code -> paste the code below.

A little bit manual, but you may try first for reference. hopefully there's other references for you

Option Compare Text
Option Explicit
Sub test()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim a As Variant
Dim c%, i%, m%
Dim ss As Range
Dim rec As String
Dim savenum%
Dim dict As Object

Set ws = Sheets("Sheet1")
Set ws2 = Sheets("sheet2")

Set dict = CreateObject("Scripting.Dictionary")
dict.comparemode = vbTextCompare

ReDim b(1 To 5000, 1 To 9)

a = ws.Range("a168:a" & Cells(Rows.Count, "A").End(xlUp).Row).Value 'Start filtering data from A168
c = 1

ws2.[a3:i10000].Clear

For Each ss In ws2.Range("a2:i2")
    dict.Add ss.Value, ss.Column
Next ss

For i = 1 To UBound(a, 1)
    If InStr(a(i, 1), "country") >= 1 Then
         If m > savenum Then
                savenum = m
            End If
        c = 1 + savenum
        b(c, 1) = a(i, 1)
        savenum = 0
    ElseIf dict.exists(a(i - 1, 1)) Then
        b(c, dict(a(i - 1, 1))) = a(i, 1)
            If m > savenum Then
                savenum = m
            End If
        m = c
        rec = a(i - 1, 1)
    
   ElseIf Not InStr(a(i, 1), "last update") >= 1 And Not dict.exists(a(i, 1)) Then
        m = m + 1
       b(m, dict(rec)) = a(i, 1)
      
    End If
   
Next i

Sheets(2).[a3].Resize(UBound(b, 1), UBound(b, 2)).Value = b
Sheet2.Columns("A").Replace what:="Country or region: ", replacement:="", searchorder:=xlByColumns

End Sub

Thank you so much for your help. I am getting an error 457 - This key is already associated with an element of this collection.
dict.Add ss.Value, ss.Column
Any suggestions on how to resolve this?

 

[RudRud]

Hi @MiteshG , Needed to copy this column in A2 (sheet2).

Do you have existing column name which's being filled more than one?

Country or region

Status of enactment

Income Inclusion Rule (IIR)

Undertaxed Payments Rule (UTPR)

Qualified Domestic Minimum Top-up Tax (QDMTT)

Covered Taxes

Qualifying Refundable Tax Credits

Transitional Safe Harbour

Compliance / Filing Requirements

Option Compare Text
Option Explicit
Sub test()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim a As Variant
Dim c%, i%, m%
Dim ss As Range
Dim rec As String
Dim savenum%
Dim dict As Object

Set ws = Sheets("Sheet1")
Set ws2 = Sheets("sheet2")

Set dict = CreateObject("Scripting.Dictionary")
dict.comparemode = vbTextCompare

ReDim b(1 To 5000, 1 To 9)

a = ws.Range("a168:a" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Value 'Start filtering data from A168
c = 1

ws2.[a3:i10000].Clear

For Each ss In ws2.Range("a2:i2")
    dict.Add ss.Value, ss.Column
Next ss

For i = 1 To UBound(a, 1)
    If InStr(a(i, 1), "country") >= 1 Then
         If m > savenum Then
                savenum = m
            End If
        c = 1 + savenum
        b(c, 1) = a(i, 1)
        savenum = 0
    ElseIf dict.exists(a(i - 1, 1)) Then
        b(c, dict(a(i - 1, 1))) = a(i, 1)
            If m > savenum Then
                savenum = m
            End If
        m = c
        rec = a(i - 1, 1)
    
   ElseIf Not InStr(a(i, 1), "last update") >= 1 And Not dict.exists(a(i, 1)) Then
        m = m + 1
       b(m, dict(rec)) = a(i, 1)
      
    End If
   
Next i

Sheets(2).[a3].Resize(UBound(b, 1), UBound(b, 2)).Value = b
Sheet2.Columns("A").Replace what:="Country or region: ", replacement:="", searchorder:=xlByColumns

End Sub

 

[MiteshG]

Hi @MiteshG , Needed to copy this column in A2 (sheet2).

View attachment 94140

Do you have existing column name which's being filled more than one?

Country or region

Status of enactment

Income Inclusion Rule (IIR)

Undertaxed Payments Rule (UTPR)

Qualified Domestic Minimum Top-up Tax (QDMTT)

Covered Taxes

Qualifying Refundable Tax Credits

Transitional Safe Harbour

Compliance / Filing Requirements

Option Compare Text
Option Explicit
Sub test()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim a As Variant
Dim c%, i%, m%
Dim ss As Range
Dim rec As String
Dim savenum%
Dim dict As Object

Set ws = Sheets("Sheet1")
Set ws2 = Sheets("sheet2")

Set dict = CreateObject("Scripting.Dictionary")
dict.comparemode = vbTextCompare

ReDim b(1 To 5000, 1 To 9)

a = ws.Range("a168:a" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Value 'Start filtering data from A168
c = 1

ws2.[a3:i10000].Clear

For Each ss In ws2.Range("a2:i2")
    dict.Add ss.Value, ss.Column
Next ss

For i = 1 To UBound(a, 1)
    If InStr(a(i, 1), "country") >= 1 Then
         If m > savenum Then
                savenum = m
            End If
        c = 1 + savenum
        b(c, 1) = a(i, 1)
        savenum = 0
    ElseIf dict.exists(a(i - 1, 1)) Then
        b(c, dict(a(i - 1, 1))) = a(i, 1)
            If m > savenum Then
                savenum = m
            End If
        m = c
        rec = a(i - 1, 1)
   
   ElseIf Not InStr(a(i, 1), "last update") >= 1 And Not dict.exists(a(i, 1)) Then
        m = m + 1
       b(m, dict(rec)) = a(i, 1)
     
    End If
  
Next i

Sheets(2).[a3].Resize(UBound(b, 1), UBound(b, 2)).Value = b
Sheet2.Columns("A").Replace what:="Country or region: ", replacement:="", searchorder:=xlByColumns

End Sub

Thank you. After pasting the column headings on Cell A2, the earlier error vanished. However, now getting a new error - Subscript out of range. On the below line - ElseIf dict.exists(a(i - 1, 1)) Then

 

[MiteshG]

Hi @MiteshG , Needed to copy this column in A2 (sheet2).

View attachment 94140

Do you have existing column name which's being filled more than one?

Country or region

Status of enactment

Income Inclusion Rule (IIR)

Undertaxed Payments Rule (UTPR)

Qualified Domestic Minimum Top-up Tax (QDMTT)

Covered Taxes

Qualifying Refundable Tax Credits

Transitional Safe Harbour

Compliance / Filing Requirements

Option Compare Text
Option Explicit
Sub test()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim a As Variant
Dim c%, i%, m%
Dim ss As Range
Dim rec As String
Dim savenum%
Dim dict As Object

Set ws = Sheets("Sheet1")
Set ws2 = Sheets("sheet2")

Set dict = CreateObject("Scripting.Dictionary")
dict.comparemode = vbTextCompare

ReDim b(1 To 5000, 1 To 9)

a = ws.Range("a168:a" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Value 'Start filtering data from A168
c = 1

ws2.[a3:i10000].Clear

For Each ss In ws2.Range("a2:i2")
    dict.Add ss.Value, ss.Column
Next ss

For i = 1 To UBound(a, 1)
    If InStr(a(i, 1), "country") >= 1 Then
         If m > savenum Then
                savenum = m
            End If
        c = 1 + savenum
        b(c, 1) = a(i, 1)
        savenum = 0
    ElseIf dict.exists(a(i - 1, 1)) Then
        b(c, dict(a(i - 1, 1))) = a(i, 1)
            If m > savenum Then
                savenum = m
            End If
        m = c
        rec = a(i - 1, 1)
   
   ElseIf Not InStr(a(i, 1), "last update") >= 1 And Not dict.exists(a(i, 1)) Then
        m = m + 1
       b(m, dict(rec)) = a(i, 1)
     
    End If
  
Next i

Sheets(2).[a3].Resize(UBound(b, 1), UBound(b, 2)).Value = b
Sheet2.Columns("A").Replace what:="Country or region: ", replacement:="", searchorder:=xlByColumns

End Sub

Hi - I was able to resolve the error. Thank you for your help. I just had one more question - Is it possible to get the data in single row as below?

Example below

Country or region	Status of enactment	Income Inclusion Rule (IIR)
Albania	No announcement yet	Entry into force: To be confirmed No announcement yet

 

[RudRud]

Book6
	A	B	C	D	E	F	G	H	I
2	Country or region	Status of enactment	Income Inclusion Rule (IIR)	Undertaxed Payments Rule (UTPR)	Qualified Domestic Minimum Top-up Tax (QDMTT)	Covered Taxes	Qualifying Refundable Tax Credits	Transitional Safe Harbour	Compliance / Filing Requirements
3	Albania	No announcement yet	Entry into force: To be confirmed No announcement yet	Entry into force: To be confirmed No announcement yet	Entry into force: To be confirmed No announcement yet	No information available.	No information available.	No information available.	No information available.
4	Angola	No announcement yet	Entry into force: To be confirmed No announcement yet	Entry into force: To be confirmed No announcement yet	Entry into force: To be confirmed No announcement yet	No information available.	No information available.	No information available.	No information available.
5	Argentina	No announcement yet	Entry into force: To be confirmed No announcement yet	Entry into force: To be confirmed No announcement yet	Entry into force: To be confirmed No announcement yet	No information available.	No information available.	No information available	No information available.
6	Armenia	No announcement yet	Entry into force: To be confirmed No announcement yet	Entry into force: To be confirmed No announcement yet	Entry into force: To be confirmed No announcement yet	No information available.	No information available.	No information available	No information available
7	Australia	In the 2023-24 Budget, the Government announced the implementation of a 15% global minimum tax and domestic minimum tax, key aspects of Pillar Two of the OECD/G20 Two-Pillar Solution to address the tax challenges arising from the digitalisation of the economy. The IIR will apply for fiscal years starting on or after 1 January 2024. The UTPR will apply for fiscal years starting on or after 1 January 2025. The domestic minimum tax will apply for income years starting on or after 1 January 2024. The draft legislation has not been released yet.	Entry into force: 1 January 2024 The IIR will apply to fiscal years that begin on or after 1 January 2024 to: Multinational groups with annual global revenue of EUR750 million or more. In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds, government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti- Base Erosion (GloBE) Rules, and income associated with international shipping	Entry into force: 1 January 2025 The UTPR will apply to fiscal years that begin on or after 1 January 2025 to: Multinational groups with annual global revenue of EUR750 million or more. In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds, government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti- Base Erosion (GloBE) Rules, and income associated with international shipping	Entry into force: 1 January 2024 The domestic minimum tax will apply to fiscal years that begin on or after 1 January 2024 to: Australian operations of multinational groups with annual global revenue of EUR750 million or more. In-scope multinationals across all sectors subject to certain exclusions for investment funds, pension funds, government entities, international organisations, not-for-profit organisations, as defined in the OECD Global anti- Base Erosion (GloBE) Rules, and income associated with international shipping	No information available.	No information available.	No information available	No information available. Federal Budget 2023-24 - Australia will implement key aspects of the OECD's 'Pillar Two' framework, including a domestic minimum tax, with an effective date for some measures from 1 January 2024. Read more
Sheet2

Option Compare Text
Option Explicit
Sub test()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim a As Variant
Dim c%, i%, m%
Dim ss As Range
Dim rec As String
Dim savenum%
Dim dict As Object

Set ws = Sheets("Sheet1")
Set ws2 = Sheets("sheet2")

Set dict = CreateObject("Scripting.Dictionary")
dict.comparemode = vbTextCompare

ReDim b(1 To 5000, 1 To 9)

a = ws.Range("a168:a" & ws.Cells(Rows.Count, "A").End(xlUp).Row).Value 'Start filtering data from A168
c = 1

ws2.[a3:i10000].Clear

For Each ss In ws2.Range("a2:i2")
    dict.Add ss.Value, ss.Column
Next ss

For i = 1 To UBound(a, 1)
    If InStr(a(i, 1), "country") >= 1 Then
         If m > savenum Then
                savenum = m
            End If
        c = 1 + savenum
        b(c, 1) = a(i, 1)
        savenum = 0
    ElseIf dict.exists(a(i - 1, 1)) Then
        b(c, dict(a(i - 1, 1))) = a(i, 1)
            If m > savenum Then
                savenum = m
            End If
        m = c
        rec = a(i - 1, 1)
 
   ElseIf Not InStr(a(i, 1), "last update") >= 1 And Not dict.exists(a(i, 1)) Then
        'm = m + 1 this one for now not running, because only one single row instead of offset rows
       b(m, dict(rec)) = a(i, 1)
 
    End If
 
Next i

ws2.[a3].Resize(UBound(b, 1), UBound(b, 2)).Value = b
ws2.Columns("A").Replace what:="Country or region: ", replacement:="", searchorder:=xlByColumns

End Sub

Hi @MiteshG , Kindly give a shot in sheet2 (do make sure a2:i2 columns are being filled -country, status of enactment, etc) .

I only hide this line (comment)

'm = m + 1 this one for now not running, because desire output only one single row instead of offset rows.