[Solved] Join rows with matching keys in first column

Discuss the spreadsheet application
Post Reply
sura
Posts: 26
Joined: Wed Jul 30, 2014 1:03 am

[Solved] Join rows with matching keys in first column

Post by sura »

Hello !

I need someone really smart to get out of that one...

As you can see on the picture that follows, I have a long sheet with values in columns A, B, C, D. The sheet is sorted by column A.

To my greatest despair, sometimes a similar A will appear twice. Then the values of B, C, D are split over several lines, instead of just being gathered in a single one, as I would need them to be.

You can see clearly the problem on the screenshot from the image below. In line 13 and 14, A value is 4433. I would therefore need the value from D14 to be moved to D13, and the line 14 deleted afterwards. Automatically of course, because the document is so long that doing it manually is unthinkable.
https://ibb.co/h7d2Jxs
https://ibb.co/h7d2Jxs
What is the best way to solve this problem?

Thank you for reading me.
Last edited by sura on Fri Oct 08, 2021 2:44 pm, edited 2 times in total.
OpenOffice 3.1 on Windows Vista
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Need a formula to automatically merge two lines into one

Post by MrProgrammer »

sura wrote:To my greatest despair, sometimes a similar A will appear twice. Then the values of B, C, D are split over several lines, instead of just being gathered in a single one, as I would need them to be.
Data → Pivot Table solved the problem for me in less than a minute.
202110071640.ods
(14.66 KiB) Downloaded 118 times
Need additional assistance? Then attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). I just need a representative sample, not the entire set of data, but I do need to see how your data is structured. Pictures of your data are almost never helpful, but the file itself would have been. Would you take a picture of your car to a mechanic and ask why it won't start?

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, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
sura
Posts: 26
Joined: Wed Jul 30, 2014 1:03 am

Re: Need a formula to automatically merge two lines into one

Post by sura »

Sample is attached. When a value in A repeats itself twice, these two lines should be merged in one, including both the values from C and D.

It appears from your sample that you have indeed found the solution.

I tried also with Pivot table, but I couldn't replicate exactly what you did. Could you please share a screenshot of the options you have entered in Pivot table tab so I know exactly how to process?

Thank you
Attachments
test sample abcd.ods
(12.25 KiB) Downloaded 99 times
OpenOffice 3.1 on Windows Vista
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Need a formula to automatically merge two lines into one

Post by robleyd »

Right click somewhere within the pivot table MrProgrammer has provided and select Edit Layout to see how it is designed.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
sura
Posts: 26
Joined: Wed Jul 30, 2014 1:03 am

Re: Need a formula to automatically merge two lines into one

Post by sura »

Thanks for reply

I did that and tried to replicate every settings from MrProgrammer.

However my table still comes out with BCD on rows instead of columns (See document : Columns A - D is how Mr Programmer successed ; Columns H - K is where I'm stuck).

Is there a final step to do afterwards?

Best regards
Attachments
202110071640 (2).ods
(12.05 KiB) Downloaded 105 times
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need a formula to automatically merge two lines into one

Post by Villeroy »

Drag the grey [Data] cell a little to the right until the mouse cursor indicates a horizontal bar.
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
sura
Posts: 26
Joined: Wed Jul 30, 2014 1:03 am

Re: Need a formula to automatically merge two lines into one

Post by sura »

fantastic.

Thanks to each and everyone of you !
OpenOffice 3.1 on Windows Vista
Post Reply