[Solved] Find same line of numbers

Discuss the spreadsheet application
Post Reply
Soricelus
Posts: 2
Joined: Mon Jan 06, 2020 7:46 pm

[Solved] Find same line of numbers

Post by Soricelus »

Hello guys,
I've tried in the last 2 days to figure out how to find similar lines in my table and I can't understand how to do it.
I've looked on multiple threads and read a lot about it but still... can't figure out hence why I'm looking to see if any of you can help me with a solution.
There are 5 columns (for 5 vans) and deliveries done across 3 years (4th one starting now) so there will be about 1000 lines.
Every day we need to do deliveries to 6 shops. In some days 1 van can cover even 4 shops, other days just 1. It depends when and where I need them.
Apart from many other data I am collecting from 3 complete years of deliveries, I need to find out if there are any exact matches of daily working vans.
Basically if the lines are looking like bellow:
0 1 2 1 0
2 0 0 2 2
1 1 1 2 1
2 0 0 2 2

I need to find the lines that are similar (not just as numbers but as position to) -> 2 0 0 2 2
All I could find till now was how to search in every column but nothing to return me an entire line that is similar with other line/lines.
Not sure if it help but since there is not a huge number of options, I would be able to search individually all the options (I'm thinking that the search might return more than 1 similar type of lines).

I have attached a file to this topic. I have 50 lines completed with data.
Is there any possibility to do it ?
Thank you in advance,
Sorin
Attachments
Cinnabon Deliveries Statistics.ods
(11.79 KiB) Downloaded 87 times
Last edited by Soricelus on Mon Jan 06, 2020 10:10 pm, edited 1 time in total.
Windows 10 Apache OpenOffice 4.1.6
FJCC
Moderator
Posts: 9277
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Find same line of numbers

Post by FJCC »

If I understood you correctly, here is one solutions. I combined all of the values from each row into a code number and then used COUNTIF() to find how many times each code number appears.
Attachments
Vans.ods
(17.46 KiB) Downloaded 109 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Soricelus
Posts: 2
Joined: Mon Jan 06, 2020 7:46 pm

Re: Find same line of numbers

Post by Soricelus »

Bloody brilliant!
I'm amazed how quick you've done it. I've looked at filters solution but lost myself further.

I thank you very much for it!
Problem solved
Windows 10 Apache OpenOffice 4.1.6
User avatar
Villeroy
Volunteer
Posts: 31279
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Find same line of numbers

Post by Villeroy »

This method fails because there are gaps in the list.
"1221" may mean any of:
<blank>1221
1<blank>221
12<blank>21
122<blank>1
1221<blank>

I simply filled up the date column and created a pivot table. You may even aggregate by year and month.
Attachments
Cinnabon Deliveries Statistics_Pivot.ods
(20.35 KiB) Downloaded 93 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply