Imported data into new table? (totally lost)

nova_sweet

New Member
Joined
Nov 27, 2016
Messages
34
Hello!

I have a importsheet with data and need to extract and count several things from it, but I have no idea how or where to start (usually I only used Excel for math problem).

I made an exampelsheet with some fake imported data and the right nr of columns, the real importsheet contains more than 5000 rows (varies from month to month and I will need to do this for each month)

What I need is to see (on a new sheet?) how many rows in total, how many from column F, how many of these (Fs) are with which attribute from column G, but not only the counting but even the percentage of the total number of rows. I need to extract this into some form of sheet where it is easy to see the countings, attributes from column F and G and the %. All this for each month and then even see some sort of number/%-count for the running year. Even the possibility to do this by each team (column J) needs to be done somehow.
Any ideas? I am feeling a bit lost in this.

I will try to insert an example:
HTML:
ABCDEFGHIJ1NrDateKNrVNrSZTempReasonJurisPlaceTeam212018-09-11234dd4rt456-jKnödelHotVar1§ xyzWarne555322018-09-12234dd5rt466-jKnödelColdWarne555432018-09-13234dd6rt457-jKnödelNot so hotVar1§ xyzWarne555542018-09-14234dd7rt458-jKnödelReally hot§ zyxWarne555652018-09-15234dd8rt459-jKnödelReally coldVar3§ zyxWarne555762018-09-16234dd9rt556-jKnödelLukewarmVar2§ zyxWarne555872018-09-17234dd10rt656-jKnödelReally cold§ zyxWarne666982018-09-18234dd11rt666-jKnödelHotVar2§ xyzWarne6661092018-09-19234dd12rt856-jKnödelColdVar3§ dddWarne555[CENTER][COLOR=#161120][B]Blad1[/B][/COLOR][/CENTER]

I am using Excel 2016

Best regards

Edit: seems like I am uncapable to get this HTML thing working, should look like this:
Code:
[TABLE]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD]Nr[/TD]
[TD]Date[/TD]
[TD]KNr[/TD]
[TD]VNr[/TD]
[TD]SZ[/TD]
[TD]Temp[/TD]
[TD]Reason[/TD]
[TD]Juris[/TD]
[TD]Place[/TD]
[TD]Team[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018-09-11[/TD]
[TD]234dd4[/TD]
[TD]rt456-j[/TD]
[TD]Knödel[/TD]
[TD]Hot[/TD]
[TD]Var1[/TD]
[TD]§ xyz[/TD]
[TD]Warne[/TD]
[TD="align: right"]555[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2018-09-12[/TD]
[TD]234dd5[/TD]
[TD]rt466-j[/TD]
[TD]Knödel[/TD]
[TD]Cold[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Warne[/TD]
[TD="align: right"]555[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2018-09-13[/TD]
[TD]234dd6[/TD]
[TD]rt457-j[/TD]
[TD]Knödel[/TD]
[TD]Not so hot[/TD]
[TD]Var1[/TD]
[TD]§ xyz[/TD]
[TD]Warne[/TD]
[TD="align: right"]555[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2018-09-14[/TD]
[TD]234dd7[/TD]
[TD]rt458-j[/TD]
[TD]Knödel[/TD]
[TD]Really hot[/TD]
[TD="align: right"][/TD]
[TD]§ zyx[/TD]
[TD]Warne[/TD]
[TD="align: right"]555[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2018-09-15[/TD]
[TD]234dd8[/TD]
[TD]rt459-j[/TD]
[TD]Knödel[/TD]
[TD]Really cold[/TD]
[TD]Var3[/TD]
[TD]§ zyx[/TD]
[TD]Warne[/TD]
[TD="align: right"]555[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2018-09-16[/TD]
[TD]234dd9[/TD]
[TD]rt556-j[/TD]
[TD]Knödel[/TD]
[TD]Lukewarm[/TD]
[TD]Var2[/TD]
[TD]§ zyx[/TD]
[TD]Warne[/TD]
[TD="align: right"]555[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2018-09-17[/TD]
[TD]234dd10[/TD]
[TD]rt656-j[/TD]
[TD]Knödel[/TD]
[TD]Really cold[/TD]
[TD="align: right"][/TD]
[TD]§ zyx[/TD]
[TD]Warne[/TD]
[TD="align: right"]666[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2018-09-18[/TD]
[TD]234dd11[/TD]
[TD]rt666-j[/TD]
[TD]Knödel[/TD]
[TD]Hot[/TD]
[TD]Var2[/TD]
[TD]§ xyz[/TD]
[TD]Warne[/TD]
[TD="align: right"]666[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2018-09-19[/TD]
[TD]234dd12[/TD]
[TD]rt856-j[/TD]
[TD]Knödel[/TD]
[TD]Cold[/TD]
[TD]Var3[/TD]
[TD]§ ddd[/TD]
[TD]Warne[/TD]
[TD="align: right"]555[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Blad1[/B][/COLOR][/CENTER]
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Here is a Pivot Table of your data. Will this representation work for you?

Count of TempTemp
ReasonColdHotLukewarmNot so hotReally coldReally hotGrand Total
Var1112
Var2112
Var3112
(blank)1113
Grand Total2211219

<tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top