How can I find duplicate first four columns?

Hello!

My excel file format is as follows:

• A column: Time
• B column: Year
• C column: Day of the year
• D column: Hour

There are many duplicate values at the first four column in this excel file. For example, 4101 and 4102 row are same values in the first four columns. 4114 and 4115, 4127 and 4128 and so forth...And I'd like to remove upper row such as 4101, 4114, 4127 etc...

How can I find duplicate A column and just remove upper row?

Answer 1

[Image Analyst]

Not sure if it's the most compact code, but I think it's simple and intuitive, and I think this will work:

[numbers, strings, raw] = xlsread('test.xlsx');
col1 = numbers(:, 1);
[C, ia, ic] = unique(col1);
fprintf('Length of column 1 = %d.\nNumber of unique numbers in column 1 = %d.\n', ...
  length(col1), length(C));
% Scan down array and keep track of the first time we encounter anumber.
rowsToDelete = zeros(1, length(C));
for k = 1 : length(C)
  % Find out how many times this unique number occurs.
  indexes = (col1 == C(k));
  numOccurrences = sum(indexes);
  fprintf('For k = %d, found %d occurrences of %d\n', k, numOccurrences, C(k));
  % More than 2?  Then delete the first one.
  if numOccurrences >= 2
    % If there are two or more, delete the first one ONLY.
    rowsToDelete(k) = find(indexes, 1, 'first');
  end
end
% Remove zeros
rowsToDelete(rowsToDelete <= 0) = [];
col1(rowsToDelete) = [];
fprintf('Deleted these rows: ');
fprintf('%d ', rowsToDelete);
fprintf('\nAfter removing first numbers we now have %d numbers.\n', ...
  length(col1));

[Suat YAZICI]

I checked many duplicated values. Your coding works very well for first column.

I just try compherending this code.

Is edited column A 'C variable' now?

[Image Analyst]

col1 contains the vector with the first duplicate rows deleted. You can delete the same rows from the other columns if you want.

[Suat YAZICI]

This code removes duplicated A-th column. How to remove with respect to duplicated variables name A-th row like A3685:I3685, A4114:I4114 and so on?

[Image Analyst]

I don't understand. I thought I gave you what you want. So if A = [2,4,6,6,6,7,8,8] the output will be [2,4,6,6,7,8] which is the same thing but with the first element (only) of duplicated runs being removed. Please give a small example, not one with tens of thousands of rows, and say which rows specifically are to be removed.

[Suat YAZICI]

For example,

• test(28x10) matrix

In addition to my question, I can see deleted these rows are 10th(10,1), 14th(14,1) and 27th(27,1) when I use your coding but delete these rows should be like (10, :), (14, :) and (27, :). I mean all lines about duplicated these rows.

[Image Analyst]

Sorry, I don't understand your grammar. You say my code deletes rows 10, 14, and 27, and it should delete rows 10, 14, and 27 (the same). So it's working, right?

[Suat YAZICI]

Sorry for misunderstanding. I tried to say that it deleted as rows 10,14,27 and columns 1. For example, I'd like to delete A10:J10 (rows 10, colums :), A14:J14, A27:J27 for this file.

[Image Analyst]

Just say

numbers(rowsToDelete) = []

to delete those rows in all columns.

[Suat YAZICI]

Sorry but it doesn't work. Why didn't I get edited 25x10 matrix instead of 25x1 matrix because of col1 = numbers(:, 1) ?

[Image Analyst]

I was just using col1 as a convenience to figure out what rows need to be deleted. Once I figured that out, I applied it to all columns in "numbers" to delete those rows from every column. If numbers is 10 columns, then it should still be 10 columns after deleting some rows. It just won't have as many rows. If you didn't get a new height for numbers, then you didn't use the code from my last comment where I deleted the rows from numbers.