How to customize dynamic tabular data from web into excel

Question

How to customize dynamic data taken from web into excel

Example Data taken from Web getting displayed as below when paste into excel/word:

1
Application has not up for the below reasons:
a. Low Processor Speed
b. Power failure
c. Under Maintenance 
All Application issues resolved on 10/04
2
System has to process instructions defined
System has processed these instructions:
Executing commands
Sending auto email
Logging Response status in LogViewer
3
Send email
Email sent to the below group:
BackEnd Team
Network users
Group MGRS
4
Start the application
Application started successfully

The expected tabular format as:

Sl.No     Description                                        Comments
1         Application has not up for the below reasons:      All Application issues resolved on 10/04
          a. Low Processor Speed
          b. Power failure
          c. Under Maintenance     
2         System has to process instructions defined         System has processed these instructions:
                                                             Executing commands
                                                             Sending auto email
                                                             Logging Response status in LogViewer

3         Send email                                         Email sent to the below group:
                                                             BackEnd Team
                                                             Network users
                                                             Group MGRS
4         Start the application                              Application started successfully

I tried with Text to columns or delimited etc but not getting as expected output.

Can you post a link to the website, perhaps there is a better way for you to copy or paste the data... — Dan, Oct 17 '18 at 10:15
This is impossible. Why? Because for No 1 all lines but the last go into description. But for No 2 only the first line goes to description and the others go to comments. How can the computer decide that? I see no pattern here. • No pattern means not possible! • Therefore you can only solve this if you show us the original data (of the website) you already lost information (which text is which column) while you extracted it to Excel/Word. So the way you extract the data is already wrong and needs to be fixed. — Pᴇʜ, Oct 18 '18 at 06:07

Wizhi · Answer 1 · 2018-10-17T14:34:21.947

The code assumes there are no blank cells in your data and I put the initial data in Column A.

The First part of the code is to modify the code for your needs.

You set the sheet name: ActiveWorkbook.Worksheets("Sheet1")

You decide which row to start (including header): startrow = 1

And also what names that should be in the new columns you are moving to: SheetName.Cells(1, 2) = "Sl.No" etc..

Notice that the code create a whole new table (copy and paste), the old column with data will be deleted at the end.

VBA Code

Sub MoveCells()

Dim lrow As Integer
Dim lrowno As Integer
Dim lrowname As Integer
Dim lrowcity As Integer

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim startrow As Integer
Dim SheetName As Worksheet


'################## Set variables ##################
Set SheetName = ActiveWorkbook.Worksheets("Sheet1") 'Name your worksheet
startrow = 1 'Set row number you want to sart the loop from

'Name first header rows
SheetName.Cells(1, 2) = "Sl.No" 'Column B = 2
SheetName.Cells(1, 3) = "Name" 'Column C = 3
SheetName.Cells(1, 4) = "City" 'Column D = 4


'################## Execute Code ##################
lrow = Cells(Rows.Count, 1).End(xlUp).Row 'Check the last row in Column A.

For i = startrow + 1 To lrow Step 3 'start from row 1 and add 1 row. Then loop to row lrow. After every loop it jump 3 rows.
lrowno = Cells(Rows.Count, 2).End(xlUp).Row 'Find last row in column B
SheetName.Cells(lrowno + 1, 2) = SheetName.Cells(i, 1) 'Copy Cells from Column A to Column B's last row
Next i

For j = startrow + 2 To lrow Step 3 'start from row 1 and add 2 rows. Then loop to row lrow. After every loop it jump 3 rows.
lrowname = Cells(Rows.Count, 3).End(xlUp).Row 'Find last row in Column C
SheetName.Cells(lrowname + 1, 3) = SheetName.Cells(j, 1) 'Copy Cells from Column A to Column C's last row
Next j

For k = startrow + 3 To lrow Step 3 'start from row 1 and add 3 rows. Then loop to row lrow. After every loop it jump 3 rows.
lrowcity = Cells(Rows.Count, 4).End(xlUp).Row 'Find last row in Column D
SheetName.Cells(lrowcity + 1, 4) = SheetName.Cells(k, 1) 'Copy Cells from Column A to Column D's last row
Next k

'Delete first column
Columns(1).EntireColumn.Delete

End Sub

What is the logic in the updated question.... seems like some rows are randomly going to the "description" column while other is going to the "comments"... — Wizhi, Oct 17 '18 at 21:10
Why did you use 3 loops if you could do it in one? Looks a way complicated to me. Also you must not use `Integer` for row counting variables. Excel has more rows than `Integer` can handle. It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. • And yes the OP already lost the information about "which text belongs to which headline" during data extraction from website to Excel. Therefore you cannot solve this at all. — Pᴇʜ, Oct 18 '18 at 06:15

Dan · Answer 2 · 2018-10-17T10:54:45.740

0

You can do it with this formula, changing the last parameter from +1 up to +3 for each column of your output.

=INDEX(A:A,(ROW()-1)*3+1)

The image shows the formula used for your third column

Note that if your input or output data do not start in row 1, then you need to change it as follows:

i.e. put the formula =INDEX($A:$A,(ROW(A1)-1)*3+ROW($A$6)+COLUMN(A3)-1) in the cell in which you want your output to start. Change the $A:$A to the column containing your input data and $A$6 to the cell where your input data start.

edited Oct 17 '18 at 10:54

answered Oct 17 '18 at 10:12

Dan

45,079
17
88
157

This not resolving the expected output when sharing the report with the data. Any alternate ways? – venkat Oct 17 '18 at 10:34
@venkat what does that mean? You need to be specific. Note that because it's using the `ROW` formula, it relies on this being in row 1 and referencing row 1. Also, I have literally pasted your input data directly into Excel and you can see your exact output data shown which is generated by the formula so you need to be a lot more specific about what you want if it's not the exact input and output in your question. – Dan Oct 17 '18 at 10:42
Note that if your input data or output data do not start in row 1, then here is a more general formula `=INDEX($A:$A,(ROW(A1)-1)*3+ROW($A$6)+COLUMN(A3)-1)`. This assumes the input data are in column A and it starts in row 6. It doesn't matter where the output data are. Just drag it across and down – Dan Oct 17 '18 at 10:50
3

This is a completely different question – Dan Oct 17 '18 at 16:29

score -2 · Answer 3 · answered Oct 17 '18 at 10:12

Try using Keep Source Formatting to keep the formatting of whatever you copied.

Shortcut is--> CTRL + K

Link to the article about pasting to retain formatting --> https://support.office.com/en-us/article/copy-cell-data-and-paste-attributes-only-0636593a-985c-4c34-bcfa-049f470a6596

How to customize dynamic tabular data from web into excel

3 Answers3