Select Single Item from column

Discuss the spreadsheet application

Select Single Item from column

Postby agmccall » Sat Jan 09, 2021 4:19 pm

Hello

I hope I can phrase this question correctly.

I purchase data and this data comes in spread sheet form. Usually it has 300 to 500 thousand records (Rows)

One of the columns is the referring url. I can easily sort by referring url.

Some of these url's provide thousands of these records.

What I want to do is export each single url to a file. I only want 1 each. So for instance if abcd.com sends 3000 records I want in file just 1 abcd.com to show

I want to be able to see all the urls in a quick glance

I hope this makes sense

al
Open Office 4.1 on Windows 7
agmccall
 
Posts: 4
Joined: Sat Jan 09, 2021 4:13 pm

Re: Sort Single Item from column

Postby MrProgrammer » Sat Jan 09, 2021 5:23 pm

agmccall wrote:What I want to do is export each single url to a file. I only want 1 each. So for instance if abcd.com sends 3000 records I want in file just 1 abcd.com to show
You want to summarize your data. Create a pivot table with the URL as the Row Field, then save that. Read about pivot tables in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3859
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sort Single Item from column

Postby agmccall » Sun Jan 10, 2021 4:16 pm

Thanks Mr Programmer. The pivot table worked. To expand on this, is there a way to add the total. I did not see this when creating the table. Example: www.abcd.com provides 1,500 records. so when I create the pivot table, I create it so it creates a new sheet, column 1 would be the referring URL and column 2 would be the total records for that URL

Thank you
Open Office 4.1 on Windows 7
agmccall
 
Posts: 4
Joined: Sat Jan 09, 2021 4:13 pm

Re: Sort Single Item from column

Postby robleyd » Mon Jan 11, 2021 12:41 am

When creating/editing the pivot table, double click on the column name in the data field listing to modify the function applied.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3644
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Sort Single Item from column

Postby agmccall » Mon Jan 11, 2021 4:54 pm

robleyd wrote:When creating/editing the pivot table, double click on the column name in the data field listing to modify the function applied.

Hello and Thanks

I did as you said but do not seem to get the total number of each referral. I have tried different inputs such as count, Sum, etc.

Thanks

Al
Open Office 4.1 on Windows 7
agmccall
 
Posts: 4
Joined: Sat Jan 09, 2021 4:13 pm

Re: Sort Single Item from column

Postby Hagar Delest » Mon Jan 11, 2021 5:59 pm

Can you upload a sample file (see: How to attach a document here)?
LibreOffice 7.0.3 on Xubuntu 20.10 and LibreOffice 6.4.6 (portable) on Windows 10.
User avatar
Hagar Delest
Moderator
 
Posts: 29258
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Sort Single Item from column

Postby MrProgrammer » Thu Jan 14, 2021 3:38 am

agmccall wrote:To expand on this, is there a way to add the total.
As before:
• URL → Row Fields
Then:
• URL → Data Fields → Options → Count → OK → OK

There was an example for Count in the link I gave you: User Guides (PDF) → Calc Guide → Ch8 - Using the DataPilot (an older name for Pivot Table. Reading the guide would have produced a solution quicker than waiting for a response on the forum and you would have learned more about the capabilities of a Pivot Table. Also, I encourage you to read the short Ten Concepts link from my first post if you have not done so. It does not directly address your question, but will save you much time and frustration if you are a beginner. Asking questions on a forum instead of reading the available documentation is a very slow way to learn.

If you need any additional assistance, attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Create a new topic unless the question is related to your first post.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Last edited by MrProgrammer on Thu Jan 14, 2021 7:04 pm, edited 4 times in total.
Reason: Amend quote attribution
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3859
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Select Single Item from column

Postby agmccall » Sun Jan 17, 2021 4:08 pm

Hello, I am attaching 2 files one is the pivot table and the other is the file I want totals of. I have only included the "LeadURL" column as the other columns contain private information

forum.ods
Sorted data need total of individual sources
(42.96 KiB) Downloaded 4 times


pivot table.ods
(45.74 KiB) Downloaded 1 time


Thank You
Open Office 4.1 on Windows 7
agmccall
 
Posts: 4
Joined: Sat Jan 09, 2021 4:13 pm

Re: Select Single Item from column

Postby MrProgrammer » Sun Jan 17, 2021 5:40 pm

MrProgrammer wrote:As before:
• URL → Row Fields ⬅︎ ⬅︎ ⬅︎ ⬅︎ Directions you followed
Then:
• URL → Data Fields → Options → Count → OK → OK ⬅︎ ⬅︎ ⬅︎ ⬅︎ Directions you did not follow

I selected a cell in your pivot table attachment → right-click → Edit Layout.
Screen Shot 2021-01-17 at 09.25.05.png
This shows that the Data Fields area is empty. Why do you not follow the instructions I provide? If you are having difficulty understanding pivot tables, read the documentation. I gave you a link for that but it seems you have not looked at it. You would not have needed your second post on 2021-01-10, nor your third post a week later, if you had read about pivot tables when I provided the link on 2021-01-09.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3859
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: robleyd and 14 guests