Excel Help, Again

[Bunt4aTriple]

Not sure if I should repurpose an old thread or start a new one.

Let me preface this by saying that I'm a dummy, but I would like to take 4, binary columns and turn that into a single column that could have multiple values, delimited by a comma.

So this:

District1	District2	District3	District4
1	0	0	0
0	0	1	0
1	1	1	1

Would become:

District

1

3

1,2,3,4

I will dip my toe into stackoverflow, but quickly get overwhelmed. The delimited entries are where I'm getting lost.

 

[Bergs]

Not sure if I should repurpose an old thread or start a new one.

Let me preface this by saying that I'm a dummy, but I would like to take 4, binary columns and turn that into a single column that could have multiple values, delimited by a comma.

So this:

District1	District2	District3	District4
1	0	0	0
0	0	1	0
1	1	1	1

Would become:

District

1

3

1,2,3,4

I will dip my toe into stackoverflow, but quickly get overwhelmed. The delimited entries are where I'm getting lost.

DM me your email and I'll send it over.

 

[GreenMonster49]

Are you running Excel 365, or some other version? (Excel 365 has some text functions that make adding the commas fairly easy.)

 

[Bunt4aTriple]

Are you running Excel 365, or some other version? (Excel 365 has some text functions that make adding the commas fairly easy.)

Well, the problem is that my company does, but I'm trying to assist a client in getting me the file I need. Not sure if they do or not.

 

[luckiestman]

I might be missing something. Why can’t they just save it as a csv file

 

[GreenMonster49]

If you can use Excel 365, I would have two columns. E2 (which you can copy down to E3 and E4 in your example) would have the formula
=IF(A2,1,"")&IF(B2,2,"")&IF(C2,3,"")&IF(D2,4,"")

F2 would have
=TEXTJOIN(",",,MID(E2,SEQUENCE(LEN(E2)),1))

 

[InstaFace]

first thought: do an if() where if it's 1/True, you append the column number to an array, and then you join the array on a comma delimiter. Because otherwise, you get commas where they don't belong.

second thought: if there's a reasonable number of columns (like just 4, here), you just concatenate a string with a bunch of if() statements, like if(c3=1, ",3", "") [note that the string being concatenated is comma-number], and then remove any leading or trailing commas as the case may be.

 

[tonyandpals]

Not sure if I should repurpose an old thread or start a new one.

Let me preface this by saying that I'm a dummy, but I would like to take 4, binary columns and turn that into a single column that could have multiple values, delimited by a comma.

So this:

District1	District2	District3	District4
1	0	0	0
0	0	1	0
1	1	1	1

Would become:

District

1

3

1,2,3,4

I will dip my toe into stackoverflow, but quickly get overwhelmed. The delimited entries are where I'm getting lost.

You're kind of describing two different things. Wanting a comma delimited file, but then your expected output really isn't that (as you're dropping the 0s.

So is the expected output really as you say, or is it:

District1,District2,District3,District4
1,0,0,0
0,0,1,0
1,0,0,0
1,1,1,1

I guess a good question is, how is your expected output to be used?

 

[Bergs]

If you can use Excel 365, I would have two columns. E2 (which you can copy down to E3 and E4 in your example) would have the formula
=IF(A2,1,"")&IF(B2,2,"")&IF(C2,3,"")&IF(D2,4,"")

F2 would have
=TEXTJOIN(",",,MID(E2,SEQUENCE(LEN(E2)),1))

Ooo. I didn't know about textjoin. THAT'S HOT.

Edit: I also didn't know about sequence. I enjoyed your solution very much!

 

[Bergs]

You're kind of describing two different things. Wanting a comma delimited file, but then your expected output really isn't that (as you're dropping the 0s.

So is the expected output really as you say, or is it:

District1,District2,District3,District4
1,0,0,0
0,0,1,0
1,0,0,0
1,1,1,1

I guess a good question is, how is your expected output to be used?

Note to self: do not let @tonyandpals assemble furniture that needs directions.

 

[tonyandpals]

Note to self: do not let @tonyandpals assemble furniture that needs directions.

Furniture comes with directions?

 

[GreenMonster49]

Ooo. I didn't know about textjoin. THAT'S HOT.

Edit: I also didn't know about sequence. I enjoyed your solution very much!

There's actually a simpler version. E2 could have:
=TEXTJOIN(",",TRUE,IF(A22=1,COLUMN(A22),""))

The problem with this is that it expects the columns that represent 1-4 to be A-D.
But if you had row 1 be 1, 2, 3, and 4 in A1 through D1, with Row 2 being the District Names, and the data starting in Row 3, then E3 (the formula for the first row) could be:
=TEXTJOIN(",",TRUE,IF(A33=1,$A$1:$D$1,""))
and this will work even if you add columns to the left of the data.

 

[Bergs]

Furniture comes with directions?

Only the really high-end stuff.

 

[Bergs]

There's actually a simpler version. E2 could have:
=TEXTJOIN(",",TRUE,IF(A22=1,COLUMN(A22),""))

The problem with this is that it expects the columns that represent 1-4 to be A-D.
But if you had row 1 be 1, 2, 3, and 4 in A1 through D1, with Row 2 being the District Names, and the data starting in Row 3, then E3 (the formula for the first row) could be:
=TEXTJOIN(",",TRUE,IF(A33=1,$A$1:$D$1,""))
and this will work even if you add columns to the left of the data.

Stop laughing at me.

 

[GreenMonster49]

Quoted for truth...

There's actually a simpler version. E2 could have:
=TEXTJOIN(",",TRUE,IF(A2: D2=1,COLUMN(A2: D2),""))

The problem with this is that it expects the columns that represent 1-4 to be A-D.
But if you had row 1 be 1, 2, 3, and 4 in A1 through D1, with Row 2 being the District Names, and the data starting in Row 3, then E3 (the formula for the first row) could be:
=TEXTJOIN(",",TRUE,IF(A3: D3=1,$A$1:$D$1,""))
and this will work even if you add columns to the left of the data.

 

[Bunt4aTriple]

You're kind of describing two different things. Wanting a comma delimited file, but then your expected output really isn't that (as you're dropping the 0s.

So is the expected output really as you say, or is it:

District1,District2,District3,District4
1,0,0,0
0,0,1,0
1,0,0,0
1,1,1,1

I guess a good question is, how is your expected output to be used?

Yeah. My use of delimited is throwing things off. The target column would be a string with commas. But to be fair, I did state that I was a dummy in the OP so give me a break, please!

 

[Bunt4aTriple]

Quoted for truth...

Okay, this is near perfect for what I need. One last ask: I do have a row where all four districts are zeros. This leaves column E blank. In that scenario, can I conditionally make column F blank as well?

 

[GreenMonster49]

Okay, this is near perfect for what I need. One last ask: I do have a row where all four districts are zeros. This leaves column E blank. In that scenario, can I conditionally make column F blank as well?

If you're using my first solution (with the SEQUENCE), what you can do is to have the F2 formula be
=IFERROR(TEXTJOIN(",",,MID(E2,SEQUENCE(LEN(E2)),1)),"")

The IFERROR outputs the empty string when the TEXTJOIN part generates an error.

 

[Bunt4aTriple]

If you're using my first solution (with the SEQUENCE), what you can do is to have the F2 formula be
=IFERROR(TEXTJOIN(",",,MID(E2,SEQUENCE(LEN(E2)),1)),"")

The IFERROR outputs the empty string when the TEXTJOIN part generates an error.

Perfect! Thanks for making me look wicked smart.

 

[Bergs]

Perfect! Thanks for making me look wicked smart.

So I get no points for being first? Tough fucking crowd, man.

 

[Bunt4aTriple]

So I get no points for being first? Tough fucking crowd, man.

I thanked you in pm, but I didn't acknowledge you here and that's on me.

 

[Bergs]

I thanked you in pm, but I didn't acknowledge you here and that's on me.
View attachment 61860

Ha! No worries, man. Just kidding.

 

[BroodsSexton]

Happy Father's Day.

I would like to take an export of transactions from a bank account, and sum up the monthly debits and monthly credits, going back two years. I have date (call it column A), debit (column b), and credit (column C) columns. Any thoughts on how to do this without breaking my head?

 

[gammoseditor]

Happy Father's Day.

I would like to take an export of transactions from a bank account, and sum up the monthly debits and monthly credits, going back two years. I have date (call it column A), debit (column b), and credit (column C) columns. Any thoughts on how to do this without breaking my head?

A pivot table is great for this. Under insert select pivot table. It should default to select your range and create in a new sheet. Drag the date selection to the row labels box. Drag both the debits and credits to the sum values. The date parameters may default to years. But you can change it to months. I think you just right click and select months for that but not 100% sure.

 

[luckiestman]

It’s pivot tables, just to have better instructions, here is the bot walk thru


You can use a pivot table in Excel to easily sum up the monthly debits and credits. Here's how you can do it:
1. Select the data range including the headers (columns A, B and C).
2. Click on the `Insert` tab and then click on `Pivot Table`.
3. In the `Create PivotTable` dialog box, make sure the `Select a table or range` option is selected and that the `Table/Range` field shows the correct data range.
4. Choose where you want the pivot table report to be placed and click `OK`.
5. In the PivotTable Fields pane, drag the `Date` field to the `Rows` area, and the `Debit` and `Credit` fields to the `Values` area.
6. Right-click on any date in the pivot table and select `Group`. In the `Group By` dialog box, select `Months` and `Years`, then click `OK`.

This will group your data by month and year, and show you the sum of debits and credits for each month. You can then filter the data to only show the last two years.

 

[BroodsSexton]

A pivot table is great for this. Under insert select pivot table. It should default to select your range and create in a new sheet. Drag the date selection to the row labels box. Drag both the debits and credits to the sum values. The date parameters may default to years. But you can change it to months. I think you just right click and select months for that but not 100% sure.

dammit you're speaking a language I almost recognize.

 

[BroodsSexton]

It’s pivot tables, just to have better instructions, here is the bot walk thru


You can use a pivot table in Excel to easily sum up the monthly debits and credits. Here's how you can do it:
1. Select the data range including the headers (columns A, B and C).
2. Click on the `Insert` tab and then click on `Pivot Table`.
3. In the `Create PivotTable` dialog box, make sure the `Select a table or range` option is selected and that the `Table/Range` field shows the correct data range.
4. Choose where you want the pivot table report to be placed and click `OK`.
5. In the PivotTable Fields pane, drag the `Date` field to the `Rows` area, and the `Debit` and `Credit` fields to the `Values` area.
6. Right-click on any date in the pivot table and select `Group`. In the `Group By` dialog box, select `Months` and `Years`, then click `OK`.

This will group your data by month and year, and show you the sum of debits and credits for each month. You can then filter the data to only show the last two years.

OK, this is very close. But it's just counting the number of transactions in each month. It's not giving me the cash flow, which is what I need.




OK--I got it. Change the field value from "count" to "sum" and then add another column that gives me the monthly cash flow by adding the two up. Nice. SoSH at its best... Thank you.

 

[gammoseditor]

dammit you're speaking a language I almost recognize.

I could have done the step by step walk through better, but wanted to jump back in and note any excel demo from the below YouTube channel is easy to follow and very helpful. I recently had to learn power bi for a work project and a video on the feed was a huge help as well.

View: https://youtu.be/UsdedFoTA68

 

[Rick Burlesons Yam Bag]

If you can use Excel 365, I would have two columns. E2 (which you can copy down to E3 and E4 in your example) would have the formula
=IF(A2,1,"")&IF(B2,2,"")&IF(C2,3,"")&IF(D2,4,"")

F2 would have
=TEXTJOIN(",",,MID(E2,SEQUENCE(LEN(E2)),1))

How is TEXTJOIN different from CONCATENATE? It seems like the syntax is slightly different but the function and outcome are the same.

 

[Bergs]

How is TEXTJOIN different from CONCATENATE? It seems like the syntax is slightly different but the function and outcome are the same.

In Excel, both the TEXTJOIN and CONCATENATE functions are used to combine text from multiple cells into a single cell. However, there are some differences between them:

1. Functionality: CONCATENATE is a basic function that concatenates (joins) multiple text strings together. It takes individual text strings or cell references as arguments and combines them into a single string. It doesn't provide any additional options for delimiters or ignoring empty cells.
   TEXTJOIN, on the other hand, is a more versatile function introduced in Excel 2016 (and later versions) that offers additional capabilities. It allows you to join text strings using a specified delimiter and can also ignore empty cells during the concatenation process.
2. Delimiters: CONCATENATE doesn't have built-in support for delimiters. If you want to include a delimiter between the concatenated text strings, you need to manually add it as an argument within quotation marks.
   TEXTJOIN, however, allows you to specify a delimiter that is automatically inserted between the joined text strings. This eliminates the need for manual insertion and provides more control over the output.
3. Ignoring empty cells: CONCATENATE doesn't have a built-in option to skip or ignore empty cells during concatenation. If any of the referenced cells are empty, CONCATENATE includes an empty string in the final result.
   TEXTJOIN includes an optional argument that allows you to ignore empty cells. By specifying the "ignore_empty" argument as TRUE, TEXTJOIN excludes empty cells from the concatenation process. This can be useful when dealing with datasets that may contain empty cells.

Overall, TEXTJOIN is a more powerful function for joining text in Excel, offering delimiter support and the ability to ignore empty cells. It provides greater flexibility and convenience compared to the CONCATENATE function, particularly in scenarios where you need more control over the concatenation process.


Edit: ChatGPT is fun.

 

[Rick Burlesons Yam Bag]

In Excel, both the TEXTJOIN and CONCATENATE functions are used to combine text from multiple cells into a single cell. However, there are some differences between them:

1. Functionality: CONCATENATE is a basic function that concatenates (joins) multiple text strings together. It takes individual text strings or cell references as arguments and combines them into a single string. It doesn't provide any additional options for delimiters or ignoring empty cells.
   TEXTJOIN, on the other hand, is a more versatile function introduced in Excel 2016 (and later versions) that offers additional capabilities. It allows you to join text strings using a specified delimiter and can also ignore empty cells during the concatenation process.
2. Delimiters: CONCATENATE doesn't have built-in support for delimiters. If you want to include a delimiter between the concatenated text strings, you need to manually add it as an argument within quotation marks.
   TEXTJOIN, however, allows you to specify a delimiter that is automatically inserted between the joined text strings. This eliminates the need for manual insertion and provides more control over the output.
3. Ignoring empty cells: CONCATENATE doesn't have a built-in option to skip or ignore empty cells during concatenation. If any of the referenced cells are empty, CONCATENATE includes an empty string in the final result.
   TEXTJOIN includes an optional argument that allows you to ignore empty cells. By specifying the "ignore_empty" argument as TRUE, TEXTJOIN excludes empty cells from the concatenation process. This can be useful when dealing with datasets that may contain empty cells.

Overall, TEXTJOIN is a more powerful function for joining text in Excel, offering delimiter support and the ability to ignore empty cells. It provides greater flexibility and convenience compared to the CONCATENATE function, particularly in scenarios where you need more control over the concatenation process.


Edit: ChatGPT is fun.

Thanks for this. The Empty cell option doesn't come up in 99% of my use cases, but this would be useful there.