HOWTO: Maintain a common shared list of hidden ads and search results across your characters

[DemonPriestessSahala]

Taken from a comment on #163

Problem

If you like to prune your in-client character search results or hidden-ad lists, but you spread your time across multiple characters, you'll quickly find that your lists are unsynchronized, you perform a lot of repeated manual work to hide the same results, and you spend more time trying to import between characters.

Solution

Fortunately, there are ways to merge the lists from all (or groups) of your characters into one common list, and then distribute that common list back to all (or groups) of your characters.

• For example, you can have one hide list for all your characters, one hide list for your female characters and one for your males, multiple hide lists by interests, and so on.

Note: this looks like an ominous wall of text. But, the actual process of merging, sorting, formatting, and exporting only takes about ten to twenty minutes to merge lists for twenty characters with an old version of Excel, and this is really only needed every few weeks or more.

• Google Sheets makes this easier than Excel! But then you have to be mindful about what you're pasting into your Google account... (some character names are not cloud-friendly)

As always, log out of F-Chat and close the program entirely before doing this. Back up before you do anything that involves manipulating these files. The batch file below will try to back up your existing list before it acts, but your data = your responsibility.

Collecting values

• Using a plaintext editor, open the hiddenUsers file in each of your profiles.
  As a reminder, these exist at C:\Users\(your Windows username)\AppData\Roaming\fchat\data\(your character name)\settings\hiddenUsers. You can see the syntax in the comment above.
• Copy the contents of the square brackets to the clipboard, but not the square brackets themselves.
• Paste as a row into your favorite spreadsheet program.
• Use the spreadsheet editor's Text to Columns functionality to turn the clipboard's very long string into separate cells.
  • For Excel, this icon lives on the ribbon's Data tab. The first step of the wizard asks for delimiters.
  • For Google Sheets, this is Data > Split text to columns. A small box below the menu bar asks for delimiters.
• When you are prompted, the data is comma-delimited.
  Both spreadsheet programs have a selectable option to choose comma-separated values. The text-to-columns operation produces a row with many, many cells.
• Select all cells on the row and copy them to the clipboard. Keyboard shortcuts are your friend.
• Select a blank cell in column A that's a few rows down. Right-click that cell and:
  • Excel: Click Paste Special > Paste Special and check Transpose. (Transpose is also one of the many small icons, if your eyes are better than mine.)
  • Sheets: Click Paste Special > Transposed.
    Your values are now in a column, rather than a row. Feel free to delete the row.
• Paste this column onto a new worksheet, one column for each character.
• Repeat this as necessary to scoop up values from all of your characters.

Now your data should look like this:

"Hidden Name 1"     "Hidden Name 1"
"Hidden Name 2"     "Hidden Name 3"

Merging lists in Excel

There are several ways to merge and remove duplicates, depending on which version of Excel you have installed. Feel free to use your own method; here's one that should work for most.

• Take each column and concatenate it onto the end of the previous column.
  Unless your version of Excel supports functions like TEXTJOIN, you may have to do this manually. In that case, copy all of the values from a column on the right and paste them at the end of the column to the left. Keyboard shortcuts are your friend.
  For example:

"Hidden Name 1"     "Hidden Name 1"
"Hidden Name 2"     "Hidden Name 3"

becomes

"Hidden Name 1"
"Hidden Name 2"
"Hidden Name 1"
"Hidden Name 3"

• When you're down to just one column that contains all of the names from each of your characters, select that whole column.
• On Excel's Data tab, pick Data Tools > Remove Duplicates.
  This opens the Remove Duplicates window.
• Unless you added them for yourself, uncheck My data has headers.
• Ensure that the Columns area shows the correct column that contains your values.
• Click OK.
  Excel removes any names that were common between characters, leaving you with a much shorter column of names.

"Hidden Name 1"
"Hidden Name 2"
"Hidden Name 3"

Merging lists in Sheets

This process is MUCH easier and faster with an array formula, which is why the collection procedure had you put values into columns by character.

• Go to the first cell in an unused column (e.g. C1).
• Paste the array formula =SORT(UNIQUE({A:A;B:B})), where A and B are the columns full of values from your characters.
  Adapt or add to the range as required. For example, if you're using columns A, B, and C, the formula goes in D1 and your range becomes A:A;B:B;C:C.
• Press Enter
  Sheets removes any names that were common between characters and sorts the list, leaving you with a much shorter column of names.
• Select the contents of the combined column and copy them to the clipboard.
• Right-click the first cell in the next column and click Paste Special > Values only.
  Working with raw values is necessary for the next part. Sheets leaves you with something that looks like this:

"Hidden Name 1"
"Hidden Name 2"
"Hidden Name 3"

Put the combined list back into CSV format

Now you have to undo the earlier transposition, which isn't difficult at all.

• Select the contents of the combined column and copy them to the clipboard.
• On a new worksheet, right-click the first empty cell and:
  • Excel: Click Paste Special > Paste Special and check Transpose.
  • Sheets: Click Paste Special > Transposed.
    Your values are now in a row again.
• Copy this row to the clipboard and open a plaintext editor.
• Paste the contents of the clipboard into the text editor.
  When they first come out of Excel or Sheets, your values are separated by tabs.
• Use your editor's Find/Replace functionality to replace tabs with commas.
  For Notepad, this is Edit > Replace. Since you cannot directly type a tab character, copy one from the pasted data and paste it into the Find what field. Put a single comma in the Replace with field.

"Hidden Name 1"     "Hidden Name 2"     "Hidden Name 3"

becomes

"Hidden Name 1","Hidden Name 2","Hidden Name 3"

• Put square brackets around this text to match the original hiddenUsers file syntax:

["Hidden Name 1","Hidden Name 2","Hidden Name 3"]

• Save this file in C:\Users\(your Windows username)\AppData\Roaming\fchat\data\ and give the file a unique name. For example: NewHiddenAdList.txt
• Close the file.

Make a list of your character folders

The automation needs this list and you can reuse it later without more work.

• Open another plaintext editor window.
• In Explorer, navigate to C:\Users\(your Windows username)\AppData\Roaming\fchat\data\(your character name)\settings\
• Copy the folder path to the clipboard and then paste it into the text editor as a new line.
• Repeat as necessary for all the characters to which you want to share the combined list.
  Each character path should be on a separate line.
• Save this file in C:\Users\(your Windows username)\AppData\Roaming\fchat\data\ with a unique name. For example: ListOfChars.txt
  Your file contents should look like this:

C:\Users\Me\AppData\Roaming\fchat\data\MyCharacter1\settings
C:\Users\Me\AppData\Roaming\fchat\data\MyCharacter2\settings
C:\Users\Me\AppData\Roaming\fchat\data\MyCharacter3\settings

• Close the file.

If you want to maintain separate hide lists for groups of characters, just make more lists of character folders.

The batch file

Here's a handy batch file that takes (as command-line arguments) a list of character folders and a combined list of hidden users.

• Open another plaintext editor window.
• Paste the following:

@echo off

: Get the current date to use as part of the backup filename
for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "datestamp=%YYYY%%MM%%DD%"

: Get the supplied filename for the list of characters who will receive the new common list
set charlist=%1

: Get the supplied filename for the new common list
set newlist=%2

@echo on

: Take the provided list of characters as input, back up the existing list, and copy the new common list over the existing list
for /f "usebackq delims=" %%i in (%charlist%) DO (
    copy "%%i\hiddenUsers" "%%i\hiddenUsers.backup_%datestamp%"
    copy "%newlist%" "%%i\hiddenUsers"
)

• Save this file as C:\Users\(your Windows username)\AppData\Roaming\fchat\data\ShareHiddenAdList.bat
• Close the file.

Pushing updates back to the characters

Now you can use the Windows command prompt (Start > search for "cmd") to work with the batch file:

cd "C:\Users\(your Windows username)\AppData\Roaming\fchat\data\"
ShareHiddenAdList.bat ListOfChars.txt NewHiddenAdList.txt

Replace the filenames with whatever you saved earlier. The batch file syntax is:
ShareHiddenAdList.bat <list-of-characters> <common-list>

Many of you can already see what the batch file is doing. But here's the most important part of the output:

copy "C:\Users\Me\AppData\Roaming\fchat\data\MyCharacter1\settings\hiddenUsers" "C:\Users\Me\AppData\Roaming\fchat\data\MyCharacter1\settings\hiddenUsers.backup_20230614"
 copy NewHiddenAdList.txt "C:\Users\Me\AppData\Roaming\fchat\data\MyCharacter1\settings\hiddenUsers"
)
        1 file(s) copied.
        1 file(s) copied.

The first line is the batch file making a backup of your existing list, in case you need to roll back. The second line is the batch file copying the common list to each of your character folders, with the correct name.

The takeaway

After you do this the first time, you shouldn't need to touch the batch file again. If you make or delete characters, remember to update the folder list for next time. Then, all you need to do is make a new common hidden-character list every so often, and use the batch file to distribute it to your characters.

Now that I have my own list of character profile paths handy, I may write some VBA to automatically pull and reformat the existing lists for each character, then dump them together into a single spreadsheet, which is most of the manual work. In the meantime, if anyone would like to contribute an easier method of merging the lists, please feel free.

Enjoy!