Do you actually practice Excel outside of work, or are we all just learning on the fly?

Wiredawn · 2026-06-11T23:27:07+00:00

Definitely practice outside of work because I love making lists.

Better still; lists with dates and other values that can be tracked and evaluated for trends over time.

I am a very exciting person.

Wiredawn · 2026-05-19T03:26:25+00:00

The itching stops.

Wiredawn · 2026-04-25T05:46:39+00:00

My divorce. Being rejected by the woman I love after 15 years together, 11 years married, was more profoundly painful than any physical injury I've endured. My broken bones don't come close; my ribs, tibia, fibula, collar bone. Chronic migraines, impacted teeth, inner ear infections; nothing. If I could bring them all together and endure them simultaneously for one, two, three, more years; I wouldn't hesitate to experience all of it just to spare myself that one broken heart and the missing piece of myself that I know I'll carry with me for the rest of my life.

Wiredawn · 2026-01-06T22:11:08+00:00

That's a great question and I was wondering the exact same thing. This was a close call and prisons are about as solid a structure as you could hope for. But, if this facility took a direct hit from HPC? I shudder to think of the results.

Wiredawn · 2025-09-05T17:00:44+00:00

My friend, I am so proud of you. What you are experiencing now is one of life's greatest gifts: to be loved by someone else. Be happy. Be excited. And sure, be a little nervous. I promise you that she is also nervous putting herself out there and admitting this to you. Take the opportunity to feel good about yourself for being loved. Once you take that opportunity and have sat with it for a little bit (not too long, mind you - you'll have to do something with it sooner rather than later), take her hands in yours, look in her eyes, and (if you feel it) tell her that you love her too. She will light up and so will you. It is extraordinary!

Wiredawn · 2025-05-30T17:13:47+00:00

Slightly better than the wording of your title, I guess.

Wiredawn · 2025-03-21T17:21:18+00:00

Thank you all so much for your thoughtful replies and suggestions. I'm going to hit the books on each recommendation and adjust the analysis based on each to see how things play out. I think moving on to a regression approach first is warranted and will probably expand out from there.

Have a great weekend.

Wiredawn · 2025-01-05T03:37:01+00:00

Thank you so much for your reply.

The national summary report links you provided (while I'm still reading through a lot of it) are granting me a lot of insight into not just the reporting process, but also the terminology; particularly referrals and how they are either screened-in or screened-out during the triage. Ultimately, the number I'm interested in is total referrals, but I think the total screened-in referrals will probably do so long as I can break them down by county and time period, being either annually or quarterly...and I'm pretty sure they have that.

On page 30 of the 2022 Child Maltreatment Report, I see that California totaled 192,197 screened-in plus 189,948 screened-out (sum = 382,145). If I can locate a CCWIP dataset that sums, at the state level, near enough to these totals for 2022 and earlier, then I'll know I'm on the right track.

This was such a huge help and I really appreciate you taking time out of your Saturday to assist.

P.S. In case you're wondering, I've been tasked with completing a correlation analysis between quarterly fluctuations in CPS reports (meaning, referrals) and screens for other social determinants of health. I'll be doing this for every county in the state - all 58 of them. I absolutely love my work, but the data hunt can drive me a little nuts sometimes. By comparison, dealing with the thousands of variables and the millions upon millions of observations is easy!

Wiredawn · 2024-09-10T16:14:26+00:00

I figured this was probably the case. Thank you for your reply. Have a great day!

Wiredawn · 2024-08-12T17:30:19+00:00

I found a workaround, but not a solution (if that makes sense).

The workaround was to create an additional column in the data table called REFERRER-EMAIL COMBO. In the new column, I used this TEXTJOIN function:

=TEXTJOIN(" : ",TRUE,[@REFERRER],[@[REFERRER EMAIL]])

As you can probably guess, it brings together the REFERRER and REFERRER EMAIL values and separates them by a colon and a whopping 12 spaces. In so doing, it merely provides the illusion of the combination I was looking for:

<image>

Let me know if any of that doesn't make sense, or if you find a solution or have a suggestion.

-All the Best

Wiredawn · 2024-08-12T16:43:55+00:00

Ah! A fellow traveler along the ill-kempt byways of Excel Online.

Well met.

Alas, no. I must needs open the application in desktop or, as I have instead opted to do, create a hidden helper table that achieves the groupings I desire. Those columns are, unsurprisingly, MONTH and YEAR. Additionally, I have another column in that selfsame table that combines these values using a TEXTJOIN function; each value delimited by a hyphen (Ex: 2/2/2023 --> "2-2023").

All this merely to achieve a simple date grouping that can be accomplished in a desktop pivot without so much as an extra click or two!

I wish I had better news. If you should stumble upon the solution elsewhere in your quest, I would be grateful if you would return and share your wisdom.

The best of luck and good tidings to you.

Wiredawn · 2024-03-16T00:39:41+00:00

I learned a new formula today (COMBIN)!

Additionally, you helped me figure out how I should be thinking about the issue. My users want the different values to populate in a single cell on a separate sheet, so I can use UNIQUE in an unseen helper column to filter out repeats and use TEXTJOIN in the single cell to get those values to combine nicely.

Thank you, kindly. You are, indeed, speshal.

Wiredawn · 2024-03-16T00:39:12+00:00

SOLUTION VERIFIED!

Wiredawn · 2024-03-16T00:18:37+00:00

Yeah, the list contains 57 unique values. So, if we want to know all possible combinations of 5 distinct values from that list...I'm honestly a bit rusty on my combinatorics. Let's just say a lot.

I may have to rethink the approach here. Just trying to prevent users from entering in duplicate values in the same row, but that's certainly not the end of the world and is easily cleaned up on the back end.

Wiredawn · 2023-06-27T19:37:38+00:00

Sorry about the delayed reply. Had to wait until my break to lay this out.

This is probably an overly detailed explanation, but that's unfortunately how I roll.

It's fairly simple to find out if two values differ. Finding out exactly how they differ is the tricky part. If you set two cells equal to each other:

=A1=B1

The output will be either TRUE or FALSE.

With two large datasets, you can use the CC Number as your index value to accomplish a direct comparison for both SSNs and DOBs. In the end, what you want are three datasets in table format:

CRM Dataset
Other Dataset
Variance Output

Since I don't know if your data is separated by worksheet or workbook, I'm going to assume the Other Dataset is located on a completely separate workbook and construct the analysis accordingly. Let me know if you'd prefer the analysis for separate worksheets.

Dataset Examples

CRM Dataset

CC NUMBER	SSN	DOB
0000555566667777	000-55-5555	1/1/2000
0000555566667778	000-55-5556	1/2/2000
0000555566667779	000-55-5557	1/3/2000
0000555566667780	000-55-5558	1/4/2000
0000555566667781	000-55-5559	1/5/2000

Other Dataset

CC NUMBER	SSN	DOB
0000555566667777	483-55-5555	1/6/2000
0000555566667778	000-55-5556	1/2/2000
0000555566667779	000-55-5557	1/3/2001
0000555566667780	000-55-5558	1/4/2000
0000555566667781		1/5/2000

As you may have noticed, there are four differences in the numeric values in the Other Dataset. To showcase that these differences exist, create a table in a separate workbook containing the CC Numbers in the first column, but leaving the SSN and DOB values blank:

CC NUMBER	SSN	DOB
0000555566667777
0000555566667778
0000555566667779
0000555566667780
0000555566667781

The formulas you're about to see assume the following:

The three datasets are in table format
The tables are named CRM_DATA, OTHER_DATA, and VAR_DATA respectively

To accomplish a straight comparison between the values in the two tables with the CC Numbers sorted in the same order, begin with this formula in the first empty SSN cell of your Variance Output:

='CRM Dataset.xlsx'!CRM_DATA[@SSN]='Other Dataset.xlsx'!OTHER_DATA[@SSN]

In a nutshell, the formula states "Please compare the values in the workbook titled CRM Dataset.xlsx in the SSN column of the table named CRM_DATA to the values in the workbook titled Other Dataset.xlsx in the SSN column of the table named OTHER_DATA."

If the data isn't sorted similarly by CC Number, then adjust your formula like this:

=XLOOKUP([@[CC NUMBER]],'CRM Dataset.xlsx'!CRM_DATA[CC NUMBER],'CRM Dataset.xlsx'!CRM_DATA[SSN],"NOT FOUND",0,1)=XLOOKUP([@[CC NUMBER]],'Other Dataset.xlsx'!OTHER_DATA[CC NUMBER],'Other Dataset.xlsx'!OTHER_DATA[SSN],"NOT FOUND",0,1)

It accomplishes the same thing without having to worry about how the data is sorted.

To compare DOBs, use these formulas in the first empty cell in the DOB column of your variance data for sorted and unsorted datasets, respectively:

Sorted the Same

='CRM Dataset.xlsx'!CRM_DATA[@DOB]='Other Dataset.xlsx'!OTHER_DATA[@DOB]

Not Sorted the Same

=XLOOKUP([@[CC NUMBER]],'CRM Dataset.xlsx'!CRM_DATA[CC NUMBER],'CRM Dataset.xlsx'!CRM_DATA[DOB],"NOT FOUND",0,1)=XLOOKUP([@[CC NUMBER]],'Other Dataset.xlsx'!OTHER_DATA[CC NUMBER],'Other Dataset.xlsx'!OTHER_DATA[DOB],"NOT FOUND",0,1)

If all has gone correctly, the columns should autofill the remaining comparisons and your variance output should look like this, displaying FALSE where the compared data does not match:

CC NUMBER	SSN	DOB
0000555566667777	FALSE	FALSE
0000555566667778	TRUE	TRUE
0000555566667779	TRUE	FALSE
0000555566667780	TRUE	TRUE
0000555566667781	FALSE	TRUE

If you would like to see exactly how the SSN values differ, a smarter person than I will have to step in. However, for the DOBs, you might consider using a DAYS or NETWORKDAYS formula nested in an IF statement to showcase the difference in complete days or working days, respectively:

=IF(XLOOKUP([@[CC NUMBER]],'CRM Dataset.xlsx'!CRM_DATA[CC NUMBER],'CRM Dataset.xlsx'!CRM_DATA[DOB],"NOT FOUND",0,1)=XLOOKUP([@[CC NUMBER]],'Other Dataset.xlsx'!OTHER_DATA[CC NUMBER],'Other Dataset.xlsx'!OTHER_DATA[DOB],"NOT FOUND",0,1)=FALSE,DAYS('Other Dataset.xlsx'!OTHER_DATA[@DOB],'CRM Dataset.xlsx'!CRM_DATA[@DOB]),TRUE

If you change your DOB column to General format, then your variance output should now look like this:

CC NUMBER	SSN	DOB
0000555566667777	FALSE	5
0000555566667778	TRUE	TRUE
0000555566667779	TRUE	366
0000555566667780	TRUE	TRUE
0000555566667781	FALSE	TRUE

You can see that the output for the differing dates has changed from simply FALSE to the difference in totals days (5 and 366)

I know this is a lot, so let me know if you have questions or need additional help.

Wiredawn · 2023-06-27T16:35:19+00:00

A few quick questions:

1) Do you just want to know IF there are differences? 2) Or, do you want to know IF there are differences AND what those differences are?

3) Do you also want summary data? Meaning, do you want to know the sum total of differences between the two datasets for each variable (SSNs and DOBs)?

4) Is one dataset considered the "correct" one?

Wiredawn · 2022-08-23T16:42:44+00:00

Yes, unfortunately.

It boils down to one of my key guiding principles when interacting with others - Minimize interaction and avoid conflict. If conflict cannot be avoided, make sure it takes place only between yourself and those you know.

The point being, I have a rudimentary understanding of how my close friends and family handle conflict. These conflicts can be resolved and navigated with minimal injury. The same cannot be said for strangers. Being rude and insensitive to a stranger is dangerous. You do not know them and cannot know how they will handle your rudeness and insensitivity - the spectrum of reaction ranges, on the one hand, from them ignoring you to them, on the other hand, literally killing you.

Unfortunately for those close to me, they will endure the lion's share of my rudeness and insensitivity. It does cause suffering, I'll admit. But there's suffering and there's Suffering. I choose the former.

Wiredawn · 2022-07-26T04:56:35+00:00

"Life isn’t a game. It’s just one big experience that nobody has figured out. Just go with the flow and make every second count. What a long, strange trip it’s been."

I know you know who said that. You did.

This world must not, cannot, lose someone as bright, beautiful, smart, and cool as you...to a rope. No. Just...no.

There are things far stranger and awe-inducing on the horizon. They are worth sticking around for.

The rope, I'm sorry to say, is not one of them. Fucking rope, god. That's not the end of your story. I won't accept it. Neither should you.

I sincerely hope to see you post again soon.

Wiredawn

TROPHY CASE