Transpose Rows to Column based on starting string of of row

[ESCAAGROVET]

Hi All,
Thanks in advance for your help.

I have a list in one column A which contains ITEM data & is repeating in nature but number of rows is not fix for each item a some item have amore attributes & some less
Each new ITEM Data starts with its image link & that cell valuee starts with https://.....followed by unique value
I want to sort this column to rows
Starting from cell A1 every subsequent row be placed in new column B,C,D,E,F,G... till https://..... is encountered


FIND DATA

https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/104453960_1.jpg

FORTUNE SOYA BARI 45 G

₹ 9.00

MRP 10.00

Margin ₹1.00

Quantity

Price/Unit

Margin

10+

8.4

1.6

1

Add

https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/103667534_1.jpg

FORTUNE SOYA CHUNKS 1 KG

₹ 105.01

MRP 135.00

Margin ₹29.99

Quantity

Price/Unit

Margin

3+

99.01

35.99

1

Add

https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/104454015_1.jpg

FORTUNE MINI SOYA 200 G

₹ 36.00

MRP 45.00

Margin ₹9.00

Quantity

Price/Unit

Margin

3+

34

11

1

Add

https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/103666453_1.jpg

FORTUNE KGMO JAR 5 L

₹ 695.00

MRP 1,004.00

Margin ₹309.00

Quantity

Price/Unit

Margin

4+

682

322

1

Add

https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/103686263_1.jpg

FORTUNE ATTA PP 10 KG

₹ 299.00

MRP 375.00

Margin ₹76.00

Quantity

Price/Unit

Margin

3+

289.91

85.09

1

Add

https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/103666549_1.jpg

FORTUNE SOYA CHUNKS 200 G

₹ 36.00

MRP 45.00

Margin ₹9.00

Quantity

Price/Unit

Margin

3+

34

11

1

Add

https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/103669451_1.jpg

FORTUNE SOY OIL TIN 15 L

₹ 1,845.00

MRP 2,525.00

Margin ₹680.00

 

[Peter_SSs]

See if this does what you want. Test with a copy of your data.

Sub Rearrange()
  Dim a As Variant
  Dim rA As Range
  
  Application.ScreenUpdating = False
  Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Range("B1")
  With Range("B1", Range("B" & Rows.Count).End(xlUp))
    .Replace What:="Add", Replacement:="#N/A", LookAt:=xlWhole
    .SpecialCells(xlBlanks).Delete Shift:=xlUp
    For Each rA In Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, 7).Areas
      rA.Resize(rA.Rows.Count - 1).Copy
      Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True
    Next rA
    Columns("B").Delete
  End With
  Application.ScreenUpdating = True
End Sub

 

[Fluff]

How many columns are you likely to end up with after transposing the data?

 

[sandy666]

another way

let
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
    Text = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    IF = Table.AddColumn(Text, "Custom", each if Text.Contains([Data], "http") then [Data] else null),
    ReplaceErr = Table.ReplaceErrorValues(IF, {{"Custom", null}}),
    FD = Table.FillDown(ReplaceErr,{"Custom"}),
    Group = Table.Group(FD, {"Custom"}, {{"Count", each Table.RowCount(_), type number}, {"All", each _, type table}}),
    List = Table.AddColumn(Group, "List", each [All][Data]),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    TSC = Table.SelectColumns(Extract,{"List"}),
    Split = Table.SplitColumn(TSC, "List", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"List.1", "List.2", "List.3", "List.4", "List.5", "List.6", "List.7", "List.8", "List.9", "List.10", "List.11", "List.12", "List.13", "List.14", "List.15", "List.16"})
in
    Split

Data		List.1	List.2	List.3	List.4	List.5	List.6	List.7	List.8	List.9	List.10	List.11	List.12	List.13	List.14	List.15	List.16
https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/104453960_1.jpg		https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/104453960_1.jpg	FORTUNE SOYA BARI 45 G	9	MRP 10.00	Margin ₹1.00	Quantity	Price/Unit	Margin	10+	8.4	1.6	1	Add
FORTUNE SOYA BARI 45 G		https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/100130689_1.jpg	FORTUNE SUNFLOWER OIL PP 1 L PK16	2364.8	MRP 2,960.00	Margin ₹595.20	Quantity	Price/Unit	Margin	1	Add
9		https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/103668435_1.jpg	FORTUNE RB OIL PP 1 L	135.01	MRP 155.00	Margin ₹19.99	Quantity	Price/Unit	Margin	44503	132.5	22.5	12+	131	24	1	Add
MRP 10.00
Margin ₹1.00
Quantity
Price/Unit
Margin
10+
8.4
1.6
1
Add
https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/100130689_1.jpg
FORTUNE SUNFLOWER OIL PP 1 L PK16
2364.8
MRP 2,960.00
Margin ₹595.20
Quantity
Price/Unit
Margin
1
Add
https://tbn-prod-assets.s3.ap-south-1.amazonaws.com/PROD_IMG/103668435_1.jpg
FORTUNE RB OIL PP 1 L
135.01
MRP 155.00
Margin ₹19.99
Quantity
Price/Unit
Margin
44503
132.5
22.5
12+
131
24
1
Add

 

[alz]

A small problem is there
In my system help value is starting from R1C0
So image link is not getting Pasted to columns

Please provide help

you can change

$A$1 to $A$2 from B2:B49 formula


here is correct formula.Try this!
=IF(OR(A2="",A2=1,A2="Add"),"","R"&SUMPRODUCT(--(LEFT($A$2:A2,6)="https:"))&"C"&COUNTA($A$2:A2)-(COUNTA($A$2:INDEX($A$2:$A$49,AGGREGATE(14,6,(ROW($A$2:A2)-ROW($A$2)+1)/(LEFT($A$2:A2,6)="https:"),1)))-1))

 

[Peter_SSs]

Perhaps I am misreading the requirement but from the expected results in post #3 and ..

So image link is not getting Pasted to columns

.. it appears to me that the hyperlink from column A at the start of each section is required to be maintained in the results - a point most of the suggestions are missing?

@alz
One other point about your suggestion: If a "1" appeared in the data other than in that row just above "Add", your formula would omit it from the results. eg if cell A4 in post #4 was 1.

 

[alz]

@alz
One other point about your suggestion: If a "1" appeared in the data other than in that row just above "Add", your formula would omit it from the results. eg if cell A4 in post #4 was 1.

Sure! Can be Workaround with below added formula!

=IF(OR(A2="",AND(A2=1,A3="",A4="Add"),A2="Add"),"","R"&SUMPRODUCT(--(LEFT($A$2:A2,6)="https:"))&"C"&COUNTA($A$2:A2)-(COUNTA($A$2:INDEX($A$2:$A$49,AGGREGATE(14,6,(ROW($A$2:A2)-ROW($A$2)+1)/(LEFT($A$2:A2,6)="https:"),1)))-1))

 

[ESCAAGROVET]

Perhaps I am misreading the requirement but from the expected results in post #3 and ..

.. it appears to me that the hyperlink from column A at the start of each section is required to be maintained in the results - a point most of the suggestions are missing?

@alz
One other point about your suggestion: If a "1" appeared in the data other than in that row just above "Add", your formula would omit it from the results. eg if cell A4 in post #4 was 1.

Thanks for your time,
Just being new here.
Requirements are met, Thanks indeed

 

[Peter_SSs]

Thanks for your time,
Just being new here.
Requirements are met, Thanks indeed

You're welcome. Glad we could help. Thanks for the follow-up.