Transpose multiple lines Horizontally in rows

Akruti Clinic · Nov 4, 2020, 7:53 PM

Hi
I have data where

chinttt:
6 PC COMBO BAGS
Price - 740+$
SHIPPING - 80 per pc
STOCK - Available in 5 COLOURS
NOTE:CONFORM COLOUR OF ACCESSORIES B4 BOOKING
image-url:test.com/huhhu

chinttt:

SPECIAL 6 PC COMBO *

◾️BOAT EARPHONE **
◾️ONE PLUS EARPHONE **
*🤗😋🦾6 PC COMBO *

BEFORE PRICE 1150*
*OFFER PRICE 1150
*SHIPPING EXTRA *
FULL SALE COMBO TAKE ORDER FAST*
image-url:test.com/huhhu

chinttt:
BACK AGAIN
*QUALITY GUARANTEED *
BRAND ::: MICHAEL KORS
PRICE ::: 450+$
SHPNG ::: 80 fix per combo if service available at given PINCODE.

i would like to transpose this in two columns fields where data is mapped correctly as under chinttt like

price details email

740                      6 PC COMBO BAGS
                      SHIPPING - 80 per pc
                      STOCK - Available in 5 COLOURS
1150              NOTE:CONFORM COLOUR OF ACCESSORIES B4 BOOKING
                      SPECIAL 6 PC COMBO *
                      ◾️BOAT EARPHONE **
                      ◾️ONE PLUS EARPHONE **
                      🤗😋🦾6 PC COMBO *
                      BEFORE PRICE 1150
                      *OFFER PRICE 1150
                      SHIPPING EXTRA *
                      FULL SALE COMBO TAKE ORDER FAST

450…so on
How can we acheive this

Terry R · Nov 4, 2020, 8:05 PM

@Akruti-Clinic said in Transpose multiple lines Horizontally in rows:

i would like to transpose this in two columns fields where data is mapped correctly as under chinttt like

My initial thought is that this will be difficult with a regular expression (regex). Regex expect some consistency in the data, regular data/formats and lines. All 3 examples provided have none of that.
The first set has Price - 740+$, second BEFORE PRICE 1150* *OFFER PRICE 1150 and third PRICE ::: 450+$. So first and third are similar but not the same (different delimiter between word and value, and also the case of the word, Price vs. PRICE) and the second has 2 prices, so which one to pick? How many other variations to the data are there?

About the only consistent item I can see is each set is started by the chinttt: At this point I would seriously consider not using regex as likely the result would be questionable.

Terry

Terry R · Nov 5, 2020, 1:41 AM

@Terry-R said in Transpose multiple lines Horizontally in rows:

Regex expect some consistency in the data, regular data/formats and lines. All 3 examples provided have none of that.

I doubt this is a sane move, but here goes. I still stand by my original assessment that the data appears to be far too variable to consider running 1 (or more) regexes over it in the hope of reformatting it. But I love a challenge and actually in so far as the process itself, it isn’t too difficult. What will be the issue is obtaining the correct Price value from each set of data. The rest of the fields don’t matter and as there is a handy delimiter between sets (chinttt:) it makes it easier in some respects. At the end of the day if it doesn’t work I’ve wasted some time in putting together a possible solution and the requester may waste a small amount of time testing it. On the other hand if it works…???

Assumptions:

MOST IMPORTANT! Each set MUST contain at least 1 set of characters Price, the case of each letter does NOT matter (price, PRICE, pRiCe etc). The numerical value captured will be the first set of number(s) following. As an example if we have Price - per 2 items @ 740+$ the regex will pick 2 since that is the first numerical value following the string Price.
In the situation where there are multiple Price in a set the first one will be used, EVERY TIME! As an example if we have

BEFORE PRICE 75*
*OFFER PRICE 1150

Then the value picked will be 75 If however the 75 was missing then 1150 will be used even though it follows the second string Price as it is the FIRST numerical value following the first string Price.
3. Blank Lines will be removed during this process, that is if any sets contain a blank line (even a line containing multiple spaces), it will be removed.

I started with:

chinttt:
6 PC COMBO BAGS
Price - 740+$
SHIPPING - 80 per pc
STOCK - Available in 5 COLOURS
NOTE:CONFORM COLOUR OF ACCESSORIES B4 BOOKING
image-url:test.com/huhhu

chinttt:

SPECIAL 6 PC COMBO *
◾️BOAT EARPHONE **
◾️ONE PLUS EARPHONE **
*🤗😋🦾6 PC COMBO *

BEFORE PRICE 1150*
*OFFER PRICE 1150
*SHIPPING EXTRA *
FULL SALE COMBO TAKE ORDER FAST*
image-url:test.com/huhhu
chinttt:
BACK AGAIN
*QUALITY GUARANTEED *
BRAND ::: MICHAEL KORS
PRICE ::: 450+$
SHPNG ::: 80 fix per combo if service available at given PINCODE.

The steps:

Replace the delimiter chinttt: with the Price numerical value followed by a single space. Find the first occurrence of Price and find the first numerical value immediately following, even if some other characters between. See assumption #1.
Replace function
Find What:^(chinttt:)(\R)(?s)(.*?price.*?((\d+)))(.*?)(?=\1|\z)
Replace With:\4 \2\3\6
Clear all blank/empty lines
Line Operations, remove empty lines (containing blank characters)
Indent all lines except the price line (1st line of set)
Replace function
Find What:^(?!\d+ \R)
Replace With: 10 spaces
Move the price down to the 1st line of data and keep indentation correct. Run repeatedly (replace all) until no more changes made
Replace function
Find What:(?-s)^(\d*?)(\d)(\h\R)(\d+)?(\h)
Replace With:\1\3\2\4
Clear all blank/empty lines
Line Operations, remove empty lines (containing blank characters)

and finished up with:

740       6 PC COMBO BAGS
          Price - 740+$
          SHIPPING - 80 per pc
          STOCK - Available in 5 COLOURS
          NOTE:CONFORM COLOUR OF ACCESSORIES B4 BOOKING
          image-url:test.com/huhhu
1150      SPECIAL 6 PC COMBO *
          ◾️BOAT EARPHONE **
          ◾️ONE PLUS EARPHONE **
          *🤗😋🦾6 PC COMBO *
          BEFORE PRICE 1150*
          *OFFER PRICE 1150
          *SHIPPING EXTRA *
          FULL SALE COMBO TAKE ORDER FAST*
          image-url:test.com/huhhu
450       BACK AGAIN
          *QUALITY GUARANTEED *
          BRAND ::: MICHAEL KORS
          PRICE ::: 450+$
          SHPNG ::: 80 fix per combo if service available at given PINCODE.

Note that the second column above IS aligned, the character in front of BOAT and ONE may not be visible making it appear as if it is misaligned.

I deliberately made the process a number of easy to understand steps. This was because trying to minimise the number of steps would have unnecessarily complicated understanding, and therefore the ability to alter to suit the data if this does not work. I do consider that possibility to be great.

So you the requester might well undertake to use this and see if the data comes out OK, if so then I strongly suggest working on a copy of the data. If the process did NOT work as expected you may want to post further questions, or if it DID work we would like to know! Consider that I (or anyone) may or may NOT supply further help due to what seems to be highly variable data that may NOT react well to this type of editing.

Terry

Akruti Clinic · Nov 5, 2020, 3:23 AM

Thanks Terry for clear explanation .With 200 entries under chintt didn’t worked .
Instead can we move the whole line with string price like
Price - 740+$ $ its data

BEFORE PRICE 1150* $ its data
*OFFER PRICE 1150

PRICE ::: 450+$ $ $ its data

Regards

Terry R · Nov 5, 2020, 5:38 AM

@Akruti-Clinic said in Transpose multiple lines Horizontally in rows:

With 200 entries under chintt didn’t worked

I suggested this would occur. You need to provide the examples in a better format, it is possible the data is being altered by the posting system. To show data without any issue, insert it into your post. Then select all the data and click on the </> button above the window. That will show it within the black box as you will see I have done in my previous post.

Are you able to identify why it did not work, was it only some data sets that did not convert? Did you check that ALL sets contain the string price followed by a number?

Terry

Akruti Clinic · Nov 5, 2020, 3:34 PM

@Terry-R said in Transpose multiple lines Horizontally in rows:

chinttt: Price -                            price 740+$
6 PC COMBO BAGS
SHIPPING - 80 per pc
STOCK - Available in 5 COLOURS
NOTE:CONFORM COLOUR OF ACCESSORIES B4 BOOKING
image-url:test.com/huhhu

chinttt:

SPECIAL 6 PC COMBO *
◾️BOAT EARPHONE **
◾️ONE PLUS EARPHONE **
*🤗😋🦾6 PC COMBO *

                                       BEFORE PRICE 1150*
                                        *OFFER PRICE 1150
*SHIPPING EXTRA *
FULL SALE COMBO TAKE ORDER FAST*
image-url:test.com/huhhu
chinttt:
BACK AGAIN
*QUALITY GUARANTEED *
BRAND ::: MICHAEL KORS
                                         PRICE ::: 450+$
SHPNG ::: 80 fix per combo if service available at given PINCODE

so if we can transpose complete line containing text price.
To be precise i am trying notepad++ to filter data from whatsapp group export chat with media which is highly unorganized with one common word price attached to every chat date wise and mobile number.

Terry R · Nov 5, 2020, 6:22 PM

@Akruti-Clinic said in Transpose multiple lines Horizontally in rows:

to filter data from whatsapp group export chat with media

Those examples look very different now. And it makes the reason why my solution failed very clear. If you are able to understand regex at all (I did try and make it fairly simple) I was looking for chinttt: on a line all by itself with a carriage return/line feed immediately behind. In your first example the price is on the same line so it doesn’t work. So that was another assumption I had made.

To make it work it is fairly simple to sort out. We just need to have chinttt on a separate line. So the first step, call it:
0.5 Replace function
Find What:^(chinttt:)
Replace With:\1\r\n

The rest of the steps will follow as already stated.
If chinttt already is on a line by itself it doesn’t matter as eventually we remove all blank lines.

One question though, why did you alter the first example in your first post so that 6 PC COMBO BAGS was before the price line where in the latest version it clearly isn’t? When we try to help we aren’t impressed in fabricated examples. As you can see now, it clearly confuses the issue.

So a note for any future requests, provide clear unaltered examples.

Terry.

Akruti Clinic · Nov 6, 2020, 10:14 AM

Thanks for Explaining well.
You question
One question though, why did you alter the first example in your first post so that 6 PC COMBO BAGS was before the price line where in the latest version it clearly isn’t?
No data changed i want to have two columns with complete line with string price to right so that shift+alt+arrow can be used to pull price separate;y to be copied to excel like column a with data and b with price…i thinks its going more comple will find another,

Terry R · Nov 7, 2020, 2:42 AM

@Akruti-Clinic said in Transpose multiple lines Horizontally in rows:

No data changed

I am referring to this

The order of the lines changed and some text was missing, namely Price - which showed on the first line immediately behind chinttt.

I am thinking that English is not your primary language as the last sentence you wrote i thinks its going more comple will find another, I do not understand. If you are still having issues, explain the problem. As you supplied some “real data” which allowed me to fix the previous problem, maybe you can show further examples where the process did not work and maybe it is still possible to update the solution to accommodate any other need.

Terry