[Solved] Multiple Cells into a Single Cell - Bulk

Discuss the spreadsheet application

[Solved] Multiple Cells into a Single Cell - Bulk

Postby arghmeinaugen » Sat Apr 04, 2020 11:44 am

Hi,

I am having issue formatting a large list of items (almost 1,200). The description of the item is in multiple cells, however there is no logic to how many cells the data is in, it is between 3 and 25 cells in a column. The items are only divided by a sequential item number in Column A and the Description is in Column C.

As follows:

Screen Shot 2020-04-04 at 10.35.47.png


I am trying to get item data onto one line (the line with Item No, Quantity and Code).

I started out with:
=IF(B24>0;E19&CHAR(10)&E20&CHAR(10)&E21&CHAR(10)&E22&CHAR(10)&E23;IF(B25>0;E19&CHAR(10)&E20&CHAR(10)&E21&CHAR(10)&E22&CHAR(10)&E23&CHAR(10)&E24;IF(B26>0;E19&CHAR(10)&E20&CHAR(10)&E21&CHAR(10)&E22&CHAR(10)&E23&CHAR(10)&E24&CHAR(10)&E25;IF(B27>0;E19&CHAR(10)&E20&CHAR(10)&E21&CHAR(10)&E22&CHAR(10)&E23&CHAR(10)&E24&CHAR(10)&E25&CHAR(10)&E26;

So, based on where the next item began, it would take all description rows before that and add together, divided by CHAR(10). With this being up to 25 lines, it would require 25x IF statements. After the first 5 it starts to go a bit wrong.

Is there any other function that could handle this? Possibly counting the number of empty cells between Item Nos in Column A and then taking the appropriate number of Description cells and adding to 1x Cell?

Any help, much appreciated.

Thanks,
Last edited by Hagar Delest on Sun Apr 05, 2020 11:29 am, edited 1 time in total.
Reason: tagged solved
Open Office 4.1.3 on Mac Sierra
arghmeinaugen
 
Posts: 11
Joined: Sat Apr 04, 2020 11:10 am

Re: Re: Multiple Cells into a Single Cell - Bulk

Postby arghmeinaugen » Sat Apr 04, 2020 12:39 pm

UPDATE:

If it helps, I have managed to get on each Items line, the number of rows of description. Using =IF(B19>0;1;IF(B20>0;2;IF(B21>0;3;IF(B22>0;4.......
Open Office 4.1.3 on Mac Sierra
arghmeinaugen
 
Posts: 11
Joined: Sat Apr 04, 2020 11:10 am

Re: Re: Multiple Cells into a Single Cell - Bulk

Postby arghmeinaugen » Sat Apr 04, 2020 1:20 pm

UPDATE:

So what I'm looking for, is to get the number of rows of description in a single cell, ideally with a paragraph between them.

Screen Shot 2020-04-04 at 12.18.25.png


Is this possible?
Open Office 4.1.3 on Mac Sierra
arghmeinaugen
 
Posts: 11
Joined: Sat Apr 04, 2020 11:10 am

Re: Multiple Cells into a Single Cell - Bulk

Postby Lupp » Sat Apr 04, 2020 1:48 pm

Welcome to the forum!

Alas! You are the next one to be told the always same fact: With VERY few exceptions images are of little worth or no worth at all concerning spreadsheet questions. Attach "the real thing": a probably reduced but "speaking" example as a .ods file.
It should contain "handmade" examples for everything you want to get.
In this case make absolutely clear if you only need the number of descriptions for any item, or if you want to get the descriptions themsleves accumulated in one or another way in your "single cell".
What did you mean by "... ideally with a paragraph between them"?

Ex ante: You have a kind of database task here. Even if you stick to spreadsheets for specific reasons, you should normailze your data. As far as I can tell from the pictures this would mean in your case:
-1- Add the "(A)" and the "(B)" value to every row concerning the item.
-2a- Create a first sheet containing a compacted table of the distinct (unique) items you can refer to.
-2b- Refer to these items using a unique "primary key" to everyone (which may be the name if the names are sufficiently simple and uniform).
-3- Not exactly "normalized", but acceptable then, you may collect additional information on any item in this sheet - like the number of related "description" or a summary of their contents or ...
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Re: Multiple Cells into a Single Cell - Bulk

Postby arghmeinaugen » Sat Apr 04, 2020 1:51 pm

Hi,

Thanks for your reply. I need to format so that I can enter into a database. The database has a single description field, therefore I need to get all cells from the description field that relate to a single item into C1.
Open Office 4.1.3 on Mac Sierra
arghmeinaugen
 
Posts: 11
Joined: Sat Apr 04, 2020 11:10 am

Re: Multiple Cells into a Single Cell - Bulk

Postby Lupp » Sat Apr 04, 2020 2:35 pm

arghmeinaugen wrote:The database has a single description field, therefore I need to get all cells from the description field that relate to a single item ...

What do you want to be placed into the "single description field"?
-1- The NUMBER of descriptions related to the distinct item under "(B)"?
-2- The descriptions themseleves? If so: In what way accumulated? Assurance concerning the mamximum length needed!
-3- References to the descriptions? If so: See above.

Basically:

The entries per distinct (unique) item are assured to be contiguous.

The database will get one record per item {distinct code under "(B)"}.

Did I guess (understand you) correctly?
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Multiple Cells into a Single Cell - Bulk

Postby MrProgrammer » Sat Apr 04, 2020 4:16 pm

Hi, and welcome to the forum.

arghmeinaugen wrote:The description of the item is in multiple cells, however there is no logic to how many cells the data is in, it is between 3 and 25 cells in a column. The items are only divided by a sequential item number in Column A and the Description is in Column C..
So, that's the logic, the number in column A.

arghmeinaugen wrote:I am trying to get item data onto one line (the line with Item No, Quantity and Code).
A1:C51 contain the original data. E1:I51 are simple auxiliary formulas. K1:M8 are rows you asked for. 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).
202004041003.ods
(17.68 KiB) Downloaded 9 times
 Edit: Updated attachment. Added terminating number in A52. I could modify formulas to avoid using A52, but this is an easy technique. 

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

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] VLOOKUP questions and answers
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: 3982
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Multiple Cells into a Single Cell - Bulk

Postby Lupp » Sat Apr 04, 2020 5:40 pm

What about
arghmeinaugen wrote:... ideally with a paragraph between them.


Well, I didn't get answers to my questions in return, and if some of my basic "assumed assurances" won't hold, probably a very different approach can be needed.

Nonetheless I will now attach two examples showing how I would try to get a solution. The one having the "_2" at the end can only work in LibreOffic V 5.4.4 or higher since it relies on a debugged TEXTJOIN().
aoo101551prepareForDatabaseImport_1.ods
(23.98 KiB) Downloaded 9 times

aoo101551prepareForDataBaseImport_2.ods
(23.66 KiB) Downloaded 7 times

The second attachment might be "toughened" for running in AOO by a piece of usercode doing the TEXTJOIN() part. I wrote it for my toolbox when the "official" version was not yet available in LibO.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Re: Multiple Cells into a Single Cell - Bulk

Postby arghmeinaugen » Sun Apr 05, 2020 11:20 am

Thanks for your reply and I have now sorted this.
Open Office 4.1.3 on Mac Sierra
arghmeinaugen
 
Posts: 11
Joined: Sat Apr 04, 2020 11:10 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests