I’m a membership organiser with a community union and spend an inordinate amount of my time prepping excel spreadsheets with membership data. Is there anything out there I could use to split the entire data set into sheets with tables of, say, 20 members each, their contact details, plus boxes for whether or not they can attend this or that event?

    • ☭ 𝗚𝗿𝗮𝗶𝗻𝗘𝗮𝘁𝗲𝗿 ☭A
      2•2 years ago

      So the contents of the file opened in a text editor will be something like this, for each row: Name,PhoneNumber

      And the output you want is Name,PhoneNumber,ExtraCriteria1,...,ExtraCriteriaN

      Do you intend to manually fill in the extra criteria columns (i.e. the columns should be blank in the output)? If so, I could just write a simple shell/Python script or something (I don’t use Windows, so if you’d like me to write an Excel script, I wouldn’t be able to test it)

      • ☭ 𝗚𝗿𝗮𝗶𝗻𝗘𝗮𝘁𝗲𝗿 ☭A
        2•2 years ago

        @Shaggy0291@lemmygrad.ml I made a script that should work for this (with placeholder columns for now, but I can fix that if you tell me what columns you want, or I could just tell you what to change). You need a Unix shell interpreter, either locally (e.g. the Bash interpreter included with Git on Windows) or through some online service (although I wouldn’t recommend that since you’d presumably be uploading sensitive data as input).

        shell script 
        #!/bin/sh

add_header() {
    name=sheet$sheet.csv
    [ -f $name ] && echo "File '$name' already exists." && exit 1
    printf 'NAME,PHONE NUMBER,SOME COLUMN,SOME OTHER COLUMN\n' > $name
}

print_sheet() {
    echo "### SHEET $sheet ###"
    cat sheet$sheet.csv
    echo
}

count=1
sheet=1
add_header
IFS=''; while read line; do
    printf '%s,,\n' $line >> sheet$sheet.csv
    if [ $count = 20 ]; then
	count=0
	print_sheet
	sheet=$(expr $sheet + 1)
	add_header
    else
	count=$(expr $count + 1)
    fi
done

print_sheet

        If you copy this to a file process.sh and you have input in some file input.csv, you can run cat input.csv | sh ./process.sh and it’ll create files sheet1.csv, sheet2.csv, etc., for each group of 20 rows

        • @Shaggy0291OP
          2•2 years ago

          You’re a legend and a true comrade! What is a unix shell interpreter?

          • ☭ 𝗚𝗿𝗮𝗶𝗻𝗘𝗮𝘁𝗲𝗿 ☭A
            2•2 years ago

            It’s just software that reads a script (like what I posted) and executes the corresponding instructions. The most popular (AFAIK) interpreter is Bash.

            If you’re using Windows 10-11 (unless it’s a 32-bit version), you can either use the built-in Bash through the “Windows Subsystem for Linux” (instructions on how to enable it here; I’ve never tried it, though, and it installs a lot more than just Bash), or install something like Git, which includes a version of Bash. I’d recommend the latter (you can just download the “thumbdrive edition” to avoid installing anything).

            If you’re using MacOS (or whatever it’s called now), it’s a lot easier since it’s partially based on Unix (just use the built-in terminal emulator).

            Let me know when you’ve got Bash working (or if something went wrong)