How to extract multiple cells in a single cell on the basis of certain condition

Help
1

abcval
How to extract sr.no 3’s item value containing multiple values in a single cell i.e sku description, Primary Barcodes. Since here all other cells having single value but few contains these multiple data.abc.xlsx (16.5 KB)

2

Hi @mhk15
It is hard to tell from this example as to which approach to suggest.

If each record is marked by the number in the first column, you could use the row index of that number as a point of reference to extract all the information from following rows until the new record number.

However, it is not clear to me weather the “Alpine Mint &” belongs to the record nr 2 or nr 3. If the latter, then it gets a bit more tricky.

3

Yes "Alpine Mint & Tea Tree Gentle Refreshing Facewash 175 " belongs to record 3rd… all other records contains their description in one cell only but here it is present in multiple cells… How to extract them all?

4

I looked at the file once again and it looks like the content of column “Tax Type%” matches every line with the “Primary Vendor SKU” and follows similar pattern every time.
If that is not always the case then it wouldn’t really work.

But if it is, you can use the logic of that column as a divider between records and then extract the data from other columns accordingly.

5

Hi

I had done something similar to this. Please find attached solution, I had converted Sheet1 input to Sheet2 of attached excel sheet test.xlsx.
You can have a look and check if it helps you in any way.
ExcelCombineCells.zip (23.8 KB)

What i have done is:

  1. Find all the rows where Key column have certain values and store as arrayOfDataRow.
  2. Pick first two rows from this array namely startRow and endRow.
  3. Search startRow index and endRow index in your main input datatable.
  4. Merge the cells between these index.

Repeat the same steps from 2-4 by picking next row.

Thanks

1 Like
6

this same function I have to perform but while executing thing I getting an error

7

1)

How to merge these cells on the basis of “S.No”?

8

can we implement this logic for multiple columns… as it is working for 2 columns only?

9

Yes you can implement this logic for multiple columns also. But if number of columns are large then it would be little difficult to concatenate each row value.
Can you share a sample excel so that i can try to merge all the rows.
Thanks

10

yeah sure…doc.xlsx (16.0 KB)
on the basis of UOM all other cells are to be merged.

11

Hi
Please find attached solution and check sheet2 for the output.
MergeExcel.zip (19.9 KB)

Thanks

12

thanks but here values for the columns MRP,Qty,Basic cost and others are not the one present in input files although cells are merged…

tbl
tbl856×392 26.2 KB

13

Sorry i forgot to put one assign after adding dataRow.
Please find updated solution and let me know if it’s correct or not.
MergeExcel.zip (726.4 KB)

Thanks

14

thanks Bharat… it worked…!!

15

Hey Bharat,
Can I apply this logic if first column contains the values that is required to be merged? like if data is in this form

doc123
doc123928×320 41.8 KB