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

fireman_max

New Member
Joined
Apr 24, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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 #1Sam P.JeffEdMark S.
PemroseJonasMark F.Frank
Tiger HouseCrandellEmmet
Fitch RoadDillionAJSam B.
Jackson (Mitch House)Crell
Jefferson #3Mark 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 #1Sam P.
Goose Hill Station #1Jeff
Goose Hill Station #1Ed
Goose Hill Station #1Mark S.
PemroseJonas
PemroseMark F.
PemroseFrank
Tiger HouseCrandell
Tiger HouseEmmet
Fitch RoadDillon
Fitch RoadAJ
Fitch RoadSam B.
Jackson (Mitch House)Crell
Jefferson #3Mark 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?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
ABCDEFGHIJKLM
1
2Cumulative Name CountStation Name Count7<--Station Count
3StationName1Name2Name3Name4Name5Name6Name7Name8Name9Name10
444Goose Hill Station #1Sam P.JeffEdMark S.
573PemroseJonasMark F.Frank
692Tiger HouseCrandellEmmet
7123Fitch RoadDillionAJSam B.
8131Jackson (Mitch House)Crell
9141Jefferson #3Mark O.
10140
11162testBob1Bob2
12
13
14
15StationName
16Goose Hill Station #1Sam P.
17Goose Hill Station #1Jeff
18Goose Hill Station #1Ed
19Goose Hill Station #1Mark S.
20PemroseJonas
21PemroseMark F.
22PemroseFrank
23Tiger HouseCrandell
24Tiger HouseEmmet
25Fitch RoadDillion
26Fitch RoadAJ
27Fitch RoadSam B.
28Jackson (Mitch House)Crell
29Jefferson #3Mark O.
30testBob1
31test0
32  
33  
34  
35  
36  
37  
fireman_max
Cell Formulas
RangeFormula
C2C2=COUNTA(C4:C11)
A4:A11A4=SUM(B$4:B4)
B4:B11B4=COUNTA(D4:M4)
C16:C37C16=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:D37D16=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),"")),"")
 
Upvote 0
Solution
Wow! KRice I don't have words... I think you just saved me a few hours a week. THANK YOU.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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"))}
 
Upvote 0
Awesome! Works perfectly. KRice, your generosity here is deeply appreciated.
 
Upvote 0
Thanks for the update. I'm happy to help. I was pleasantly surprised to discover that Sheets had such functions for transforming your table.
 
Upvote 0

Forum statistics

Threads
1,223,516
Messages
6,172,777
Members
452,477
Latest member
DigDug2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top