find text and rearrange data with VBA

erikduurt

New Member
Joined
Jun 14, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Dear experts of VBA and excel,

I'm a beginner to VBA programming and new to MrExcel. I have searched the internet and MrExcel far and wide but I can't find the solution to my problem. I have data that is not very nice arranged for analysis as it comes from a word file. The original file has 3 column and 2515 rows.
What I would like to find the next non-blank in column c, in the example its 51. When non-blank is found take the data from Column A and one row lower than the non-blank and copy it to a new sheet under column MEP. In this case the example it's "Jessica Stegrud".

The next action is similar. Again find the first non-blank in Column C which is 51. Than return the value from Column a but now 3 rows lower: "Recital 1 a (new)" to the new sheet under Column: "reference"

Same action again but copy from Column A 5 rows lower the "a" to the other sheet under column "original"
Last action is the same but not from Column B 5 rows lower the "(1 a) However, a transition to e-mobility with the purpose to reduce the emissions of carbon dioxide requires fossil-free production of electricity." to other sheet under Column: "change"

The last action is which is to find the word "justification" in Column A and past the content from the 2 rows below to the other sheet under justification. In this case it's: "Putting a value on lithium batteries and accumulators increases the interest and willingness of end users to correctly dispose batteries, which is especially important for critical material flows.


I hope I made my self clear and I hope someone can help me in the right direction or links to my intention rearrangement.

Below is the original:
Book4
AB
1Amendment
2Jessica Stegrud
3Proposal for a regulation
4Recital 1 a (new)
5Text proposed by the CommissionAmendment
6(1 a) However, a transition to e-mobility with the purpose to reduce the emissions of carbon dioxide requires fossil-free production of electricity.
7Or{EN}en</Original>
8Amendment
9Jessica Stegrud
10Proposal for a regulation
11Recital 2
12Text proposed by the CommissionAmendment
13(2) Batteries are thus an important source of energy and one of the key enablers for sustainable development, green mobility, clean energy and climate neutrality. (2) Batteries are thus an important energy carrier and one of the key enablers for sustainable development, green mobility, clean energy and climate neutrality.
14Or{EN}en</Original>
15Amendment
16Hildegard Bentele
17Proposal for a regulation
18Recital 27
19Text proposed by the CommissionAmendment
20. Parameters to be considered in safety tests should therefore be laid down for those energy storage systems. Parameters to be considered in safety tests should therefore be laid down for those energy storage systems. Furthermore, given the steadily increasing energy density in lithium-based batteries and accumulators, fire incidents endangering humans and the environment have been increasing and hampering the circular economy. To ensure safe end-of-life treatment and avoid dangerous disposal, a European deposit refund scheme shall be put in place one year after the entry into force of this Regulation. Assigning a value to batteries and waste batteries is required to avoid risks that result from incorrect disposal, in particular fire incidents endangering humans and the environment.
21Or{EN}en</Original>
22Justification
23Putting a value on lithium batteries and accumulators increases the interest and willingness of end users to correctly dispose batteries, which is especially important for critical material flows.
Sheet1


and the intended changes:
Book4
ABCDEF
1amendmentMEPsreferenceoriginal changejustification
251Jessica StegrudRecital 1 a (new)(1 a) However, a transition to e-mobility with the purpose to reduce the emissions of carbon dioxide requires fossil-free production of electricity.
352Jessica StegrudRecital 2(2) Batteries are thus an important source of energy and one of the key enablers for sustainable development, green mobility, clean energy and climate neutrality. (2) Batteries are thus an important energy carrier and one of the key enablers for sustainable development, green mobility, clean energy and climate neutrality.
460Hildegard BenteleRecital 27. Parameters to be considered in safety tests should therefore be laid down for those energy storage systems. Parameters to be considered in safety tests should therefore be laid down for those energy storage systems. Furthermore, given the steadily increasing energy density in lithium-based batteries and accumulators, fire incidents endangering humans and the environment have been increasing and hampering the circular economy. To ensure safe end-of-life treatment and avoid dangerous disposal, a European deposit refund scheme shall be put in place one year after the entry into force of this Regulation. Assigning a value to batteries and waste batteries is required to avoid risks that result from incorrect disposal, in particular fire incidents endangering humans and the environment.Putting a value on lithium batteries and accumulators increases the interest and willingness of end users to correctly dispose batteries, which is especially important for critical material flows.
Sheet2
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,175,517
Messages
5,897,895
Members
434,685
Latest member
asdf12l3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top