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

MiteshG · Jun 23, 2023

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

MiteshG · Jun 25, 2023

RudRud said:
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

VBA Code:

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.

Thank you again for your response. I tried to execute this and got output as below. For the headings where there is text that is spanning more than 1 rows, the output seems to have only last line as below

Country or region	Status of enactment	Income Inclusion Rule (IIR)
Australia	The draft legislation has not been released yet.	Base Erosion (GloBE) Rules, and income associated with international shipping

RudRud · Jun 25, 2023

MiteshG said:
Thank you again for your response. I tried to execute this and got output as below. For the headings where there is text that is spanning more than 1 rows, the output seems to have only last line as below

Country or region Status of enactment Income Inclusion Rule (IIR)
Australia The draft legislation has not been released yet. Base Erosion (GloBE) Rules, and income associated with international shipping

sheet1 data is starting from a168 correct? Meanwhile sheet2 columns are from A2:I2?

Would you mind to share the file instead into the dropbox/gdrive for further checking?

MiteshG · Jun 25, 2023

RudRud said:
sheet1 data is starting from a168 correct? Meanwhile sheet2 columns are from A2:I2?

Would you mind to share the file instead into the dropbox/gdrive for further checking?

Hi @RudRud - Sheet1 data is starting from row 1. Here is the google drive link - Google Drive: Sign-in

RudRud · Jun 25, 2023

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

No information available.

4

Angola

No announcement yet

Entry into force: To be confirmed No announcement yet

No information available.

5

Argentina

No announcement yet

Entry into force: To be confirmed No announcement yet

No information available.

No information available

No information available.

6

Armenia

No announcement yet

Entry into force: To be confirmed No announcement yet

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. 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

8

Austria

No announcement yet. Austria has to implement the Pillar Two rules in line with the Council Directive (EU) 2022/2523 of 14 December 2022 on ensuring a global minimum level of taxation for multinational enterprise groups and large-scale domestic groups in the Union.

Entry into force: To be confirmed No announcement yet. It is expected that Austria will follow the overall implementation timeline proposed by the Directive (EU) 2022/2523 of 14 December 2022, i.e., 2024 for the IIR and 2025 for the UTPR.

Entry into force: To be confirmed No announcement yet. Austria can adopt a QDMTT as part of its overall implementation of Pillar Two under Art. 11 of the Directive (EU) 2022/2523 of 14 December 2022

No information available.

No information available

No information available.

9

Azerbaijan

No announcement yet

Entry into force: To be confirmed No announcement yet

No information available.

No information available

10

Bahamas

On May 17 the Bahamas government published a public consultation gthe Green Paper on Corporate Income Tax Strategies for the Bahamash, which sets out the options under consideration for transitioning away from the existing business licence tax (BTL) regime, as well as implementing changes to address Pillar Two

Entry into force: To be confirmed No announcement yet

No information available.

No information available

11

Bahrain

No announcement yet

Entry into force: To be confirmed No announcement yet

No information available

No information available.

No information available

12

Barbados

The Prime Minister announced In the Budgetary Proposals and Financial Statement 2023 on 14 March 2023 that the Government was deep in consultations with the Barbados Revenue Authority. The Prime Minister indicated that she would likely come back to the country within six months on this issue.

Entry into force: To be confirmed No announcement yet

No information available

No information available.

13

Belgium

The Belgian Minister of Finance has announced a phased tax reform, with one phase dedicated to Pillar Two enactment in Belgium. Currently, stakeholder consultations are taking place with advisors and businesses. Draft legislation is expected to be published in the summer 2023, with a vote shortly after that. In general, the Pillar Two implementation will be in line with the OECD model rules and the Council Directive (EU) 2022/2523 of 14 December 2022 on ensuring a global minimum level of taxation for multinational enterprise groups and large-scale domestic groups in the Union. The government already agreed on some core principles of the Pillar Two implementation in Belgium including a decision that a QDMTT will be introduced, that the Pillar Two QDMTT will be payable in the course of the year (via the Belgian tax prepayment system) and that the R&D tax credit will be modified to qualify as a qualifying tax credit.

Entry into force: 1 January 2024 It is expected that Belgium will follow the overall implementation timeline proposed by the Directive (EU) 2022/2523 of 14 December 2022, i.e., 2024 for the IIR and 2025 for the UTPR.

Entry into force: 1 January 2025 It is expected that Belgium will follow the overall implementation timeline proposed by the Directive (EU) 2022/2523 of 14 December 2022, i.e., 2024 for the IIR and 2025 for the UTPR.

Entry into force: To be confirmed Most likely Belgium will include a QDMTT in the draft legislation.

No information available.

The Government has expressed that the R&D tax credit will be modified to qualify as a qualifying tax credit.

It is expected that Belgium will follow the Transitional Safe Harbour guidance included in the Implementation Framework issued by the OECD in December 2022.

No information available. Belgium agrees on core principles for implementation of the Global Minimum Tax (GloBE/Pillar 2) for MNEfs and some additional tax measures The Belgian government reached an agreement on the Federal budget. After long discussions within the government, a number of measures have been decided that will reduce expenditures and measures that will increase revenue. Read more

14

Bermuda

In the 2023 Budget, the Bermuda Premier announced plans for the second half of 2023 on a decision in relation to Pillar Two (and potentially a QDMTT) and the 8,000 exempted companies in Bermuda which are in possession of a certificate from the Bermuda Government that states that they will not be charged taxes on their income until 2035 (should an income tax be enacted). There is a committee of industry representatives formed with a report to the Government for consideration due this Summer 2023 (June / July).

Entry into force: To be confirmed No announcement yet

No information available

No information available.

Sheet2

VBA Code:

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("a1: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 Left(a(i, 1), 10) = "country or" 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
ws2.Cells.WrapText = true 
End Sub

MiteshG · Jun 25, 2023

RudRud said:
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
8 Austria No announcement yet. Austria has to implement the Pillar Two rules in line with the Council Directive (EU) 2022/2523 of 14 December 2022 on ensuring a global minimum level of taxation for multinational enterprise groups and large-scale domestic groups in the Union. Entry into force: To be confirmed No announcement yet. It is expected that Austria will follow the overall implementation timeline proposed by the Directive (EU) 2022/2523 of 14 December 2022, i.e., 2024 for the IIR and 2025 for the UTPR. Entry into force: To be confirmed No announcement yet. It is expected that Austria will follow the overall implementation timeline proposed by the Directive (EU) 2022/2523 of 14 December 2022, i.e., 2024 for the IIR and 2025 for the UTPR. Entry into force: To be confirmed No announcement yet. Austria can adopt a QDMTT as part of its overall implementation of Pillar Two under Art. 11 of the Directive (EU) 2022/2523 of 14 December 2022 No information available. No information available. No information available No information available.
9 Azerbaijan 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
10 Bahamas On May 17 the Bahamas government published a public consultation gthe Green Paper on Corporate Income Tax Strategies for the Bahamash, which sets out the options under consideration for transitioning away from the existing business licence tax (BTL) regime, as well as implementing changes to address Pillar Two 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
11 Bahrain 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
12 Barbados The Prime Minister announced In the Budgetary Proposals and Financial Statement 2023 on 14 March 2023 that the Government was deep in consultations with the Barbados Revenue Authority. The Prime Minister indicated that she would likely come back to the country within six months on this issue. 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.
13 Belgium The Belgian Minister of Finance has announced a phased tax reform, with one phase dedicated to Pillar Two enactment in Belgium. Currently, stakeholder consultations are taking place with advisors and businesses. Draft legislation is expected to be published in the summer 2023, with a vote shortly after that. In general, the Pillar Two implementation will be in line with the OECD model rules and the Council Directive (EU) 2022/2523 of 14 December 2022 on ensuring a global minimum level of taxation for multinational enterprise groups and large-scale domestic groups in the Union. The government already agreed on some core principles of the Pillar Two implementation in Belgium including a decision that a QDMTT will be introduced, that the Pillar Two QDMTT will be payable in the course of the year (via the Belgian tax prepayment system) and that the R&D tax credit will be modified to qualify as a qualifying tax credit. Entry into force: 1 January 2024 It is expected that Belgium will follow the overall implementation timeline proposed by the Directive (EU) 2022/2523 of 14 December 2022, i.e., 2024 for the IIR and 2025 for the UTPR. Entry into force: 1 January 2025 It is expected that Belgium will follow the overall implementation timeline proposed by the Directive (EU) 2022/2523 of 14 December 2022, i.e., 2024 for the IIR and 2025 for the UTPR. Entry into force: To be confirmed Most likely Belgium will include a QDMTT in the draft legislation. No information available. The Government has expressed that the R&D tax credit will be modified to qualify as a qualifying tax credit. It is expected that Belgium will follow the Transitional Safe Harbour guidance included in the Implementation Framework issued by the OECD in December 2022. No information available. Belgium agrees on core principles for implementation of the Global Minimum Tax (GloBE/Pillar 2) for MNEfs and some additional tax measures The Belgian government reached an agreement on the Federal budget. After long discussions within the government, a number of measures have been decided that will reduce expenditures and measures that will increase revenue. Read more
14 Bermuda In the 2023 Budget, the Bermuda Premier announced plans for the second half of 2023 on a decision in relation to Pillar Two (and potentially a QDMTT) and the 8,000 exempted companies in Bermuda which are in possession of a certificate from the Bermuda Government that states that they will not be charged taxes on their income until 2035 (should an income tax be enacted). There is a committee of industry representatives formed with a report to the Government for consideration due this Summer 2023 (June / July). 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.
Sheet2

VBA Code:

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("a1: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 Left(a(i, 1), 10) = "country or" 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 ws2.Cells.WrapText = true End Sub

Thank you so much again for your response but I seem to be getting only the last line in case there are multiple lines under any heading. Attaching the sample -

Extract_Specific_Data_from_PDF_to_Excel.xlsm

A

B

C

D

E

F

G

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

3

Albania

No announcement yet

No information available.

4

Angola

No announcement yet

No information available.

5

Argentina

No announcement yet

No information available.

6

Armenia

No announcement yet

No information available.

7

Australia

The draft legislation has not been released yet.

Base Erosion (GloBE) Rules, and income associated with international shipping

No information available.

8

Austria

and large-scale domestic groups in the Union.

Directive (EU) 2022/2523 of 14 December 2022, i.e., 2024 for the IIR and 2025 for the UTPR.

of the Directive (EU) 2022/2523 of 14 December 2022

No information available.

9

Azerbaijan

No announcement yet

No information available.

10

Bahamas

business licence tax (BTL) regime, as well as implementing changes to address Pillar Two

No announcement yet

No information available.

11

Bahrain

No announcement yet

No information available

No information available.

12

Barbados

would likely come back to the country within six months on this issue.

No announcement yet

No information available

13

Belgium

the Belgian tax prepayment system) and that the R&D tax credit will be modified to qualify as a qualifying tax credit.

of 14 December 2022, i.e., 2024 for the IIR and 2025 for the UTPR.

Most likely Belgium will include a QDMTT in the draft legislation.

No information available.

The Government has expressed that the R&D tax credit will be modified to qualify as a qualifying tax credit.

Sheet2

RudRud · Jun 25, 2023

Apologies for the confusion, Please use this one instead.

b(m, dict(rec)) = b(m, dict(rec)) & " " & a(i, 1)

Forgot to paste the changed code earlier

VBA Code:

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("a1: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 Left(a(i, 1), 10) = "country or" 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)) = 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
ws2.Cells.WrapText = true
End Sub

MiteshG · Jun 25, 2023

RudRud said:

Apologies for the confusion, Please use this one instead.

b(m, dict(rec)) = b(m, dict(rec)) & " " & a(i, 1)

Forgot to paste the changed code earlier

VBA Code:

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("a1: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 Left(a(i, 1), 10) = "country or" 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)) = 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
ws2.Cells.WrapText = true
End Sub

Thank you so much for your help. Working fine now. Again, thanks a lot for all the help.

RudRud · Jun 25, 2023

MiteshG said:
Thank you so much for your help. Working fine now. Again, thanks a lot for all the help.

Glad to assist, If you have any further questions, please don't hesitate to ask/share with us

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

MiteshG

New Member

MiteshG

New Member

Excel Facts

RudRud

Active Member

MiteshG

New Member

RudRud

Active Member

MiteshG

New Member

RudRud

Active Member

MiteshG

New Member

RudRud

Active Member

Forum statistics

Share this page

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

New Member

New Member

Excel Facts

Active Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock