Break column data into multiple rows if contains specific text.

[premjeetgupta]

Hi,
Attaching excel file for reference. I facing problem to transform data in desired format.

I want to break column data into rows data from wherever certain text "sh firmware card" exist. Column-A data will remain same for rows data.
I have mapped a sample result in sheet1 of the attached excel.

Can It be done by the formula. Please guide.

Thanks in advance.

 

[eibi]

premjeetgupta,

There may be better ways to do this than a formula -- so be careful what you wish for...

In cell A1, paste this array formula, and execute with Ctrl+Shift+Enter:

=INDEX(Sheet4!$A$1:$BC$1000,INT(SMALL(IF(ISERROR(FIND("sh firmware card",Sheet4!$A$1:$BC$100)),999999999,(ROW(Sheet4!$A$1:$BC$100)*100)+(COLUMN(Sheet4!$A$1:$BC$100)*0.00001)),ROW()))/100,1)

In cell B1, paste this one, and similarly, execute with C+S+E, and drag to fill thru Column K:

=INDEX(Sheet4!$A$1:$BC$1000,INT(SMALL(IF(ISERROR(FIND("sh firmware card",Sheet4!$A$1:$BC$100)),999999999,(ROW(Sheet4!$A$1:$BC$100)*100)+(COLUMN(Sheet4!$A$1:$BC$100)*0.00001)),ROW()))/100,(MOD(SMALL(IF(ISERROR(FIND("sh firmware card",Sheet4!$A$1:$BC$100)),999999999,(ROW(Sheet4!$A$1:$BC$100)*100)+(COLUMN(Sheet4!$A$1:$BC$100)*0.00001)),ROW()),1)*100000)+(COLUMN()-2))

Take note that this formula doesn't know when to stop, so if it runs out of data for Column K, it begins to borrow content from the next row. I've flagged this with red text for your attention in attached file.

As I mentioned -- a formula probably isn't the best way to handle this; if the source data gets significantly larger, it may gum up your processor. Be cautious.

eibi

 

[premjeetgupta]

@eibi Thank for your response.
The formula did work for me. Yes, is It also making system little slow and the minor issue of repeat text which you have highlighted in red text.
Actually, I have 15 files like the sample sheet. I add all the sheets one below the main file. The result goes over 8000+ rows.
But still this formula saving my lot of time.

Please guide, If there is any other way to do this task.
Appreciate for your efforts and time.

 

[p45cal]

The table in the attached starting at cell A10 of Sheet1 is the Power Query result of transforming your data. Right-click somewhere in the table and choose Refresh to update it.
It's actually a straightforward transformation in Power Query but it may need to be made a bit more flexible if, for example, your initial data has more than 55 columns.
However, you say you have 15 files like this. This too could be handled by Power Query (it was designed to do this).
It might be even simpler if the data comes not in Excel but some other type of data file… does it?

 

[premjeetgupta]

@p45cal The result is perfect.
Could you please guide me how did you do it in Power Query step by step. I'm new with Power Query.
The many files are in .CSV format.

Thanks.

 

[p45cal]

I'm not going to go through it step by step but show you how you can go through it step by step.
First get the data into Power Query Editor.
Select any cell among your data on Sheet4 and click as below


Confirm the next dialogue box and since your data has no headers, make sure that too is correct:


Click OK and it will take you into the Power Query Editor.

I'm going to stop there and get you to look at the file I attached, so that you can get to the same place but after some more steps:
In that file, select any cell in the result table on Sheet1, right-click and coose Table then Edit Query…
This opens the same editor as before, but on the right you will see Applied Steps, with (usually) the bottom one selected:


You can click on the name of any of these steps to observe what happens in the table on the left, also you can click on the gear wheel to the right of the name to open a dialogue box to adjust what happens. Also look at the formula bar to see what code has been written automatically for you; you can edit this directly if you want:


When finished editing, close out by clicking:


That's all.

If you could attach one or two .csv files I could create a query to deal with them directly.

 

[premjeetgupta]

Thanks your very much @p45cal . These steps are very helpful for me.
I have to just change column range in power query for Replaced Values and Merged Columns for my other sheets which has data in more than 55-120 columns. That's it.
Exit and load and refresh the sheet. Voila results are done.