Start new row If cell contains "SOC" and merge all rows until next instance of "SOC"

**FriendlyBear** · 08-09-2021, 04:02 PM

Hey there!

Long time lurker, new poster

I am trying to help clean up data in an attempt to streamline a data entry input process for ordering.

The report I receive is extracted from another program that's output displays key information across 3 and sometimes 4 rows (Dirty_Data.xlsx). This is because the vendor has tabs/spaces in the description that are copying and pasting. Unfortunately I need it to be on 1 row so I can copy and paste it into my ordering program.

Every new row of information starts with "SOC". I have attached what the data should turn into in a perfect world (test_clean_copy.xlsx)

The first entry to every row would start with Column A, then Column B, then Column A again, and again, and possibly again, until there is a number value in column B, then it would be B, C, D, E, F, G.

Apologies if my verbiage makes this one harder to help with, I can clarify anything, thank you in advance for any help or guidance!

Happy Monday

**JeteMc** · 08-13-2021, 06:20 PM

Hello FriendlyBear and Welcome to Excel Forum.
The following seems to work for most instances:
1. For the "SOC" cells in column J: =IFERROR(INDEX(A$1:A$26,AGGREGATE(15,6,ROW(A$1:A$26)/(LEFT($A$1:$A$26,3)="soc"),ROWS(A$1:A1))),"")
2. For the cells in column K:

Formula:

Please Login or Register  to view this content.

3. For columns L:Q =IF(J8="","",INDEX(B$3:B$28,AGGREGATE(15,6,ROW(B$1:B$26)/(ISNUMBER($B$3:$B$28)),ROWS(B$1:B1))))
Let us know if you have any questions.

Start new row If cell contains "SOC" and merge all rows until next instance of "SOC"

LinkBack

Thread Tools

Rate This Thread

Display

Start new row If cell contains "SOC" and merge all rows until next instance of "SOC"

Re: Start new row If cell contains "SOC" and merge all rows until next instance of "SOC"

Thread Information

Users Browsing this Thread

Similar Threads

Budget assist: Entering "Weekly" "Fortnightly" "Monthly" OR "Yearly" with formulas

Using "index", "match", "min", "abs", to find the closest value in multiple criteria

[SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"

"Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"

[SOLVED] How to USE """"" cells count """"" change font color

[SOLVED] Hide/unhide excel rows as per answer i.e. "Yes", "No", "Not Applicable"

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions