Missing values per case

iotapsi321

New Member
Joined
Oct 6, 2017
Messages
17
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,759
Office Version
2007
Platform
Windows
You can put several examples covering all possible cases, there you indicate which are the rows that should be deleted.
Also comments, in which row the records begin and in which column the data to review begins
 

iotapsi321

New Member
Joined
Oct 6, 2017
Messages
17
Ok thanks. As an example, data begins on row 1 column 1. If a row is missing more than 2 values in columns A - D (variable 1), more than 3 values in columns E - J (variable 2), and more than 2 values in K- N (variable 3); then, I would like for that row to be deleted. Is this what you were looking for?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,759
Office Version
2007
Platform
Windows
There are only 3 variables?
Are they always those columns?
Should the 3 conditions be met?
 

iotapsi321

New Member
Joined
Oct 6, 2017
Messages
17
There are more than these 3, perhaps too many to list. Just wanted to provide and example. Yes the data is always in those columns and the main condition to be met is that across each of the 3 variables there should be no more than 50% of missing values.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,759
Office Version
2007
Platform
Windows
I'm sorry but I am not understanding.
You put three examples:

A - D (variable 1),
E - J (variable 2),
K- N (variable 3)


1. Are there more variables?

2. Is there a pattern to know how many columns are there per variable?

3. What is the condition?
a) If only one variable is met.
b) The 3 variables must be met.

Please, could you answer in that order.

It would also be great if you put 10 examples of the data you have on your sheet.

*If contains confidential information, you could replace it with generic data.
 

iotapsi321

New Member
Joined
Oct 6, 2017
Messages
17
I apologize for the lack of clarity. Below is a sample of generic data. I would like to delete participants 1,3, & 8 because they have over 50% of missing values on all 3 variables (so the condition is 50% across all 3 variables, which is why participant 6 retained and is not deleted). There is no consistent pattern for variables. Some variables have more items (columns) than others. Thanks again for all your help!

Id#QMI1QMI2QMI3QMI4QMI5QMI6IAI1IAI2IAI3IAI4IAI5IAI6IAI7IAI8CT1CT2CT3CT4CT5
1667422443
2776777434212442222
3
41111192342324422122
577779343234666
67683342235442224
75664533532355221312
86693422222
9555558222222455555
10777778244224321124

<colgroup><col><col span="19"></colgroup><tbody>
</tbody>




<tbody>
</tbody>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,759
Office Version
2007
Platform
Windows
We need to find a pattern to determine the total columns per variable, I don't need to explain why?

QMI1 QMI2 QMI3 QMI4 QMI5 QMI6 (In this case the pattern could be QMI?)
IAI1 IAI2 IAI3 IAI4 IAI5 IAI6 IAI7 IAI8 (In this case the pattern could be IAI?)
CT1 CT2 CT3 CT4 CT5 (In this case the pattern could be CT?)

Those letters could be the pattern, that is, if the macro eliminates the numerical value, the letters remain, and that way we know how many columns are for the variable QMI, IAI and CT.

It makes sense to you.
 

iotapsi321

New Member
Joined
Oct 6, 2017
Messages
17
Oh yes that does. Here is the pattern of the ACTUAL variable names:
RelAttendReligiousSpiritual DepAnxCombatHmechngDADplyexpStressRelationDplyRelDiscrimUnitStressExp

<tbody>
</tbody>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,759
Office Version
2007
Platform
Windows
Do you have that list of variables in some other sheet, in some column as a list?

Or You can create that list on some sheet, say "variable" sheet in Column A starting in cell A2.
With that I start the macro.
 

Forum statistics

Threads
1,077,855
Messages
5,336,790
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top