Forum Discussion
Excel formula help
Do you have the word "Company" with each company name, or how do you know where is company name, where is person name or something else?
The word "Company" is not in each company name. But the company name is bolded. There is a company name for each entry. And then additional details in the next 3 - 5 lines. Does that answer your question? Thank you for responding! I am hoping to save someone a lot of copying and pasting!
That could be this way:
- select you entire column and copy it to the next one
- unbold first column (select and Ctrl+B)
- Ctrl+F->Options, Find what is empty, Format is bold font; Find All; Ctrl+A on bottom pane with list of cells found
- now you have all company names selected in second column. In formula bar type 0 and Ctrl+Enter. You shall have zero in that column instead of company names
- select your entire range in two columns, Data->From Table/Range, uncheck what your table has headers and start editing in Power Query editor
- add column Company (if second column 0 then company else null)
- add Index column from 0 and another one from 1
- add another column (if second column 0 then first index else null) and fill it down
- one more column (second index minus previous column minus 1)
- remove other columns but last one, company and first column
- fill down company
- select last column, Transform->Pivot column, here select Company and in Advanced - All rows
- Land you query into the Excel sheet.
That's all. Please see second sheet in attached.