Argonous
New Member
- Joined
- Sep 23, 2004
- Messages
- 15
- Office Version
- 2019
- Platform
- Windows
Hello All,
I am trying to create a macro that will take an .xml file that is opened in Excel and convert the data into a single lines of useable data. I have the simple stuff like removing the formatting in place, however I am at a loss at how to insert and delete cells based on the value of certain cells.
The first thing that the macro needs to do is insert a single cell (like a tab over) after a cell that contains a specific value. The 2nd table below shows how a single cell needs to be inserted to the right of the "1L" value in the "ID" column and 2 cells inserted to the right of the "X" value in the same column. After that, I need for the macro to delete a range of cells for the value in the "ID" column and move the data from the row below upward. Finally, any row with "1E" in the "ID" column needs to be deleted. An example of how the final line of data should look is in the 3rd table below.
Hopefully someone here can help me out. Thanks in advance!
Original Data:
Insert Single Cell:
* Inserts cell or cells to the right of the "ID" column based on the data in each cell.
Final Result:
*Deletes a range of cells and moves the data up.
I am trying to create a macro that will take an .xml file that is opened in Excel and convert the data into a single lines of useable data. I have the simple stuff like removing the formatting in place, however I am at a loss at how to insert and delete cells based on the value of certain cells.
The first thing that the macro needs to do is insert a single cell (like a tab over) after a cell that contains a specific value. The 2nd table below shows how a single cell needs to be inserted to the right of the "1L" value in the "ID" column and 2 cells inserted to the right of the "X" value in the same column. After that, I need for the macro to delete a range of cells for the value in the "ID" column and move the data from the row below upward. Finally, any row with "1E" in the "ID" column needs to be deleted. An example of how the final line of data should look is in the 3rd table below.
Hopefully someone here can help me out. Thanks in advance!
Original Data:
Key | ID | Value | Date | Time | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
7 | L | CJ | 02/09/2021 | 11:53:09 | ||||||||||
8 | 1L | 111 | 02/09/2021 | 11:53:09 | ||||||||||
9 | X | 818R12454 | 02/09/2021 | 11:53:09 | ||||||||||
10 | 1E | OK | 02/09/2021 | 11:53:09 |
Insert Single Cell:
Key | ID | Value | Date | Time | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
7 | L | CJ | 02/09/2021 | 11:53:09 | ||||||||||
8 | 1L | 111 | 02/09/2021 | 11:53:09 | ||||||||||
9 | X | 818R12454 | 02/09/2021 | 11:53:09 | ||||||||||
10 | 1E | OK | 02/09/2021 | 11:53:09 |
Final Result:
Key | ID | Building | Room | Asset | Date | Time | ||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
7 | L | CJ | 111 | 818R12454 | 02/09/2021 | 11:53:09 |