Missing values per case

[iotapsi321]

Hello! I am new to excel and have learned a lot from these messages boards and hope to keep the learning momentum going.

I have a large dataset with over 25k rows and hundreds of columns. I would like to delete rows (participants) who have over 50% missing values per variables. Each column reflects an item for a variable. For most variables, the sum of 4-5 consecutive columns (items) constitutes a variable. So in the case of a 4 item variable, I would like delete rows in where there is no value for 3 or more items. Any guidance you can provide would be a huge help.

 

[iotapsi321]

The list of the variables and the data are in sheet 1. The variables are in the first row and the data begin in A2. Thank you so much for your help.

 

[DanteAmor]

But I mean this list:

[TABLE="width: 76"]
<tbody>[TR]
[TD="width: 76"]RelAttend[/TD]
[/TR]
[TR]
[TD]Religious[/TD]
[/TR]
[TR]
[TD]Spiritual[/TD]
[/TR]
[TR]
[TD]Dep[/TD]
[/TR]
[TR]
[TD]Anx[/TD]
[/TR]
[TR]
[TD]Combat[/TD]
[/TR]
[TR]
[TD]Hmechng[/TD]
[/TR]
[TR]
[TD]DA[/TD]
[/TR]
[TR]
[TD]Dplyexp[/TD]
[/TR]
[TR]
[TD]Stress[/TD]
[/TR]
[TR]
[TD]Relation[/TD]
[/TR]
[TR]
[TD]DplyRel[/TD]
[/TR]
[TR]
[TD]Discrim[/TD]
[/TR]
[TR]
[TD]Unit[/TD]
[/TR]
[TR]
[TD]StressExp[/TD]
[/TR]
</tbody>[/TABLE]

I need to know if you have 2 sheets, one sheet with the data and another sheet with the list of variables.


Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[iotapsi321]

Thank you for your patience. All of the data is on one sheet and there are no other sheets.

 

[DanteAmor]

But:

You can create that list on some sheet, say "variable" sheet in Column A starting in cell A2.

Variable
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >RelAttend</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Religious</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Spiritual</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Dep</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Anx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Combat</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Hmechng</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >DA</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Dplyexp</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Stress</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Relation</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >DplyRel</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Discrim</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Unit</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >StressExp</td></tr></table>

 

[iotapsi321]

If necessary I can create another sheet that includes the list of variables in the order as you suggested. Currently, there is just one sheet with the variables in the first row and the data values beginning in row 2.

 

[DanteAmor]

But I don't know how your variables are or how your data is. It's obvious to you, but I can't see how your data is.
You could put an image or simply copy a part of your sheet and paste here.

To post a range of cells review this:

Welcome to Version 4 of the New MrExcel Message Board!

Way back in 1999, I launched the first MrExcel Message Board using a WWWBoard Perl Script from Matt's Script Archive. Later, we migrated to phpBB and then to vBulletin. The vBulletin board lasted for 12 years. But with vBulletin no longer being supported, as of November 17, 2019, we have...

www.mrexcel.com