[Solved] Multiple Cells into a Single Cell - Bulk

[arghmeinaugen]

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:

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,

[arghmeinaugen]

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.......

[arghmeinaugen]

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.

Is this possible?

[Lupp]

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 ...

[arghmeinaugen]

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.

[Lupp]

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?

[MrProgrammer]

Hi, and welcome to the forum.

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.

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 101 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

[Lupp]

What about

... 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 96 times

aoo101551prepareForDataBaseImport_2.ods

(23.66 KiB) Downloaded 89 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.

[arghmeinaugen]

Thanks for your reply and I have now sorted this.