Stumped! Google Sheets... making the information work all match up.... not a coder... is there an basic-dude way to do it?

[fireman_max]

Hi. I am about to break my computer. I have no idea how to make this work. Hoping this can be fixed in a way a newb can understand (without coding)

I have to do this work all the time. In column 1 I get the station name.
In col 2,3, 4, 5, 6 (sometimes all the way out to 10) I get individual crew members. But different station names have different numbers of crew

Goose Hill Station #1	Sam P.	Jeff	Ed	Mark S.
Pemrose	Jonas	Mark F.	Frank
Tiger House	Crandell	Emmet
Fitch Road	Dillion	AJ	Sam B.
Jackson (Mitch House)	Crell
Jefferson #3	Mark O.

What I have to have is two columns. The first with the station name, the second with the crew member, So the above turns into this:

Goose Hill Station #1	Sam P.
Goose Hill Station #1	Jeff
Goose Hill Station #1	Ed
Goose Hill Station #1	Mark S.
Pemrose	Jonas
Pemrose	Mark F.
Pemrose	Frank
Tiger House	Crandell
Tiger House	Emmet
Fitch Road	Dillon
Fitch Road	AJ
Fitch Road	Sam B.
Jackson (Mitch House)	Crell
Jefferson #3	Mark O.

I do this manually right now by counting how many columns out the names go, inserting rows, copying the station name down as many spots as I need, and dragging all the names in one-by-one. I can't take it anymore. Can't the computer do this for me? Is there a way to do it if I'm just a regular guy and not some spreadsheet genius?

 

[KRice]

I believe there is a more efficient way to accomplish this with the improved functions available in Excel 365, but here is one approach. This uses some help columns/rows that you can hide if you don't want to see them (specifically columns A:B and row 2). If your station list grows, just pull the helper formulas down to cover all of them (in this example, I extended the coverage an extra two rows and then added a "test" station). I've added two "Bobs" to the test station to make a point. Bob1 is picked up on the bottom list, but Bob2 is not. The reason is that the names need to be filled contiguously from the left, so Bob2 would have to appear under the Name2 column to be seen. The Station-Name list builds itself based on looking back at the cumulative name counts (in column A) and the station name counts (column B) to determine which row and columns to pull information from.

MrExcel20210424.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Cumulative Name Count	Station Name Count	7	<--Station Count
3			Station	Name1	Name2	Name3	Name4	Name5	Name6	Name7	Name8	Name9	Name10
4	4	4	Goose Hill Station #1	Sam P.	Jeff	Ed	Mark S.
5	7	3	Pemrose	Jonas	Mark F.	Frank
6	9	2	Tiger House	Crandell	Emmet
7	12	3	Fitch Road	Dillion	AJ	Sam B.
8	13	1	Jackson (Mitch House)	Crell
9	14	1	Jefferson #3	Mark O.
10	14	0
11	16	2	test	Bob1					Bob2
12
13
14
15			Station	Name
16			Goose Hill Station #1	Sam P.
17			Goose Hill Station #1	Jeff
18			Goose Hill Station #1	Ed
19			Goose Hill Station #1	Mark S.
20			Pemrose	Jonas
21			Pemrose	Mark F.
22			Pemrose	Frank
23			Tiger House	Crandell
24			Tiger House	Emmet
25			Fitch Road	Dillion
26			Fitch Road	AJ
27			Fitch Road	Sam B.
28			Jackson (Mitch House)	Crell
29			Jefferson #3	Mark O.
30			test	Bob1
31			test	0
32
33
34
35
36
37
fireman_max

Cell Formulas
Range	Formula
C2	C2	=COUNTA(C4:C11)
A4:A11	A4	=SUM(B$4:B4)
B4:B11	B4	=COUNTA(D4:M4)
C16:C37	C16	=IFERROR(INDEX($C$4:$C$11,AGGREGATE(15,6,(ROW($A$4:$A$11)-ROW($A$3))/($A$4:$A$11>=ROWS(C$16:C16)),1)),"")
D16:D37	D16	=IFERROR(INDEX($D$4:$M$11,MATCH(C16,$C$4:$C$11,0),IF( COUNTIF(C$16:C16,C16)<=INDEX($B$4:$B$11,MATCH(C16,$C$4:$C$11,0)),COUNTIF(C$16:C16,C16),"")),"")

 

[fireman_max]

Wow! KRice I don't have words... I think you just saved me a few hours a week. THANK YOU.

 

[KRice]

I'm happy to help. Just post back if you run into difficulties adapting it to your sheet. If you're not familiar with the XL2BB add-in used to post many of the problems and solutions on this site, you can learn more about it and download/install it using the information in the link in my signature block. One feature of it is the clipboard icon in the upper left corner of the posted examples (intersection of row and column headings). Clicking that icon copies the sheet to your clipboard. Then if you open a new worksheet, select the same cell as shown in the upper left corner of the sample (cell A1 in this case) and paste, you'll have a working copy of the posted example without having to type or copy/paste individual formulas. You should be able to do this part of the operation even without XL2BB installed. Welcome to the Board!

I hope the solution works...you mentioned Google Sheets in your title, but also mentioned 365 in your profile. The solution posted should work in Excel...I have doubts that the functions used are supported by Google Sheets.

 

[fireman_max]

KRice-- the Legend!-- thank you for your warm welcome and help. I have one follow u question for you.

I am able to get this working in Excel which is very cool. But I am supposed to do this in Google sheets. I just read that Goolge Sheets does not recognize "aggregate" command. Is there an alternative that will work in Google sheets. If not, it is ok, I'll juts copy and paste the data from excel.

 

[KRice]

There is probably another alternative that will work in Sheets, but I don't regularly use it, so I'm not as familiar with its function set. I'll have a look and post back.

 

[KRice]

There is an alternative in Google Sheets, which has some nice array handling features and functions similar to Excel 365. This single formula builds the flattened table (or unpivoted table) that you want, assuming your Google Sheet is laid out like the Excel example above with Station names in C4:C11 and the name fields in D4:M11. Paste this formula into a cell where you want the new two-column table and it will create a table with headings "Station" and "Name", followed by the unpivoted data in your table. Note that this will ignore blanks in the name fields, but in order for the names to be picked up, a station name must appear on the same row as the name(s) in the source table.

={"Station","Name";ArrayFormula(query((split(flatten(C4:C11&"|"&D4:M11),"|")),"select * where Col2 is not null"))}

 

[fireman_max]

Awesome! Works perfectly. KRice, your generosity here is deeply appreciated.

 

[KRice]

Thanks for the update. I'm happy to help. I was pleasantly surprised to discover that Sheets had such functions for transforming your table.