Hi folks! Could use some help:

Working with GSheets

I have a list containing 100-1000 lines with the following structure.

In this example, there are 4 name, id and subs. This amount, however, can be different: from 1 to 10-15.

I need somehow to extract NAME, ID, and SUBS into separate columns.

Screenshot attached.

thanks!

Working with GSheets

I have a list containing 100-1000 lines with the following structure.

Code:

`[{"name":"VASILISA","id":"Xlkjhalksduylk71661","subs":121121}, {"name":"PEDROS","id":"90870398472lkjljssss","subs":1211}, {"name":"ActionAFool","id":"ljkhlkjhlkj798798lkjhljk","subs":59400}, {"name":"OROMERO","id":"080980lkl-8uR7w","subs":44}]`

I need somehow to extract NAME, ID, and SUBS into separate columns.

Screenshot attached.

thanks!

*****hey all, this was the only place that seemed relevant. if there is a better place to post this, please feel free to move it******

Each week I update the target prices for 75-100 stocks. I need to see over time how these target prices are trending. I've attached a s'sheet of one example I put together. BUT do I need to create a separate graph for each of the 75-100 stocks? I would prefer a graph, but if I could do it through some lookup function that returns a calculation that shows me the price change, that might suffice.

]]>Each week I update the target prices for 75-100 stocks. I need to see over time how these target prices are trending. I've attached a s'sheet of one example I put together. BUT do I need to create a separate graph for each of the 75-100 stocks? I would prefer a graph, but if I could do it through some lookup function that returns a calculation that shows me the price change, that might suffice.

I'm tracking funds for a project in which payments are made "**as needed**" instead of being on a periodic schedule (weekly, monthly, etc...). I am asking for help on how to formulate a funding burn rate and an estimated depletion date for the available funds.

My spreadsheet contains the following:

Cell H1 = Funded Amount

Cell L1 = Today's Date

Column A = Payment # (e.g. 1, 2, 3...)

Column F = Invoiced Amount

Column H = Running total of $ (Amount Paid to Date)

Additional Information:

Contract term - 12 months

Any assistance would be greatly appreciated.

]]>My spreadsheet contains the following:

Cell H1 = Funded Amount

Cell L1 = Today's Date

Column A = Payment # (e.g. 1, 2, 3...)

Column F = Invoiced Amount

Column H = Running total of $ (Amount Paid to Date)

Additional Information:

Contract term - 12 months

Any assistance would be greatly appreciated.

Please help if you can. I'm sure this is much easier than I think. I need help with a formula in "D"

Does this make sense? Can anyone help?

Thanks!

Christina Autry

Chandler, TX

]]>A - Date of Placement | B - Date Plan Due | C - Date Plan Approved | D - Review Due |

1/1/2021 | this is the date in "A" +45 | this is a date or the cell is blank | I need either the date in C +150 OR if the C is blank, I need something like "Out of Compliance" |

Does this make sense? Can anyone help?

Thanks!

Christina Autry

Chandler, TX

Dear Forum Users,

In the uploaded xls. file there is a table titled: “ALL possible combinations of NUMBERS”(__AZ13:BE22__). In this table I would like for combinations of __NUMBERS__ to appear __after__ the comparison of the two __SET__ combinations. This means that in the number combinations there shall be numbers from:

This is a graphic demonstration how the formula should make the combinations of numbers:

Basically a__number__ from __each SET__, must be in the __NUMBER combination__. Also, __maximally, only, one number__ from __each set__ must be used. In this example I used yellow background to show that __in each set of both SET combinations__ there is a number the forms __the NUMBER combination__: 1, 11, 24, 30, 31. Now, __instead of 24, I could have also picked 8 and 45 and crossed out 24__. Then the combination would consist of 6 numbers: 1, 8, 11, 30, 31, 45.

Any help would be appreciated :)

Please don’t hesitate do ask questions of something isn’t quite understandable.

Cross posted at:

https://www.excelforum.com/excel-formulas-and-functions/1348997-formula-for-creating-number-combinations-from-two-tables.html#post5515180 (last post)

https://www.mrexcel.com/board/thread...ables.1170631/

In the uploaded xls. file there is a table titled: “ALL possible combinations of NUMBERS”(

- “Identical numbers with 2nd combination plus green (Tab. 1)” (
__BA6:BE11__) and - “Identical numbers with 1st combination plus green (Tab. 2)” (
__BA27:BE32)__

This is a graphic demonstration how the formula should make the combinations of numbers:

Basically a

Any help would be appreciated :)

Please don’t hesitate do ask questions of something isn’t quite understandable.

Cross posted at:

https://www.excelforum.com/excel-formulas-and-functions/1348997-formula-for-creating-number-combinations-from-two-tables.html#post5515180 (last post)

https://www.mrexcel.com/board/thread...ables.1170631/

Hi,

I need to automate the calculation of calendar year, in the attached file, how do i get it.

The criteria is if the calendar year is complete than the count should be 1 for that year, if the calendar year is not completed than the count should be zero.

Please help.

I need to automate the calculation of calendar year, in the attached file, how do i get it.

The criteria is if the calendar year is complete than the count should be 1 for that year, if the calendar year is not completed than the count should be zero.

Please help.

Calendar Year | |

2018 | 0 |

2019 | 1 |

2020 | 1 |

2021 | 1 |

2022 | 0 |

Hi,

I use the Jira plugin to pull data from Jira into an Excel sheet, so every time the plugin runs (acc to schedule) that same data gets overwritten (i.e. everything that was there before is erased so new data takes place).

What I need to accomplish is to capture some original information from the first time the data is pulled from Jira into the spreadsheet, but I'd like to use in Excel without the use of VBA. Thus, I'd like to take a snapshot of the data when written into Excel the first time so I can calculate the sum of a specific column which will provide me with a total number I need to use for later comparison as the same data changes.

So my question is -

Is there a way to capture that in Excel somehow through a formula or some workaround that will save the original data somewhere for the first time it is written and before it gets overwritten (my Jira Plugin pulls data from Jira every hour).

I need to figure out an automated process that does this without manual intervention.

Thanks!

]]>I use the Jira plugin to pull data from Jira into an Excel sheet, so every time the plugin runs (acc to schedule) that same data gets overwritten (i.e. everything that was there before is erased so new data takes place).

What I need to accomplish is to capture some original information from the first time the data is pulled from Jira into the spreadsheet, but I'd like to use in Excel without the use of VBA. Thus, I'd like to take a snapshot of the data when written into Excel the first time so I can calculate the sum of a specific column which will provide me with a total number I need to use for later comparison as the same data changes.

So my question is -

Is there a way to capture that in Excel somehow through a formula or some workaround that will save the original data somewhere for the first time it is written and before it gets overwritten (my Jira Plugin pulls data from Jira every hour).

I need to figure out an automated process that does this without manual intervention.

Thanks!

Hi,

I want to compare if the returned data has been changed for any cell across the columns, when compared to the original data (called Existing).

I have an Existing Registry on one sheet (it is in a table) with the original data, and have a second sheet (Returned Registry) with the data returned so far, in a replica table. I have used the following formula to apply Conditional Formatting to each cell in the Returned Registry to highlight if the data is different: =A6<>INDEX('Existing Registry'!A:A,MATCH($A6,'Existing Registry'!$A:$A,0)).

I understand a user can filter rows based on colour, but there are a lot of columns to scroll through, so I now want to add a flag to the Returned Registry table that indicates if any of the columns on that row have changed. I tried testing the colour with a Function, but could not get that to work. Am hoping I can use something like SUMPRODUCT, but cannot get logic to work to test all columns based on a formula that needs to change across the columns.

Column A in both the Existing and Returned sheets contains a unique identifier, so am using that for the MATCH lookup.

This is my attempt: =SUMPRODUCT(--(A6:BB6<>INDEX('Existing Registry'!A:A,MATCH($A6,'Existing Registry'!$A:$A,0))))

A6:BB6 are the columns in the Returned Registry that I want to see are TRUE or FALSE when matched against the data in the Existing Registry. The result needs to return the TRUE / FALSE (or 1/0 due to "--") for each column in the range, but using the formula which needs to act relative. ie A6 <> INDEX('Existing Registry'!A:A, then B6 <> INDEX('Existing Registry'!B:B etc

Will mock up a file if needed, but hoping the above just needs to be tweaked

Thanks in advance.

]]>I want to compare if the returned data has been changed for any cell across the columns, when compared to the original data (called Existing).

I have an Existing Registry on one sheet (it is in a table) with the original data, and have a second sheet (Returned Registry) with the data returned so far, in a replica table. I have used the following formula to apply Conditional Formatting to each cell in the Returned Registry to highlight if the data is different: =A6<>INDEX('Existing Registry'!A:A,MATCH($A6,'Existing Registry'!$A:$A,0)).

I understand a user can filter rows based on colour, but there are a lot of columns to scroll through, so I now want to add a flag to the Returned Registry table that indicates if any of the columns on that row have changed. I tried testing the colour with a Function, but could not get that to work. Am hoping I can use something like SUMPRODUCT, but cannot get logic to work to test all columns based on a formula that needs to change across the columns.

Column A in both the Existing and Returned sheets contains a unique identifier, so am using that for the MATCH lookup.

This is my attempt: =SUMPRODUCT(--(A6:BB6<>INDEX('Existing Registry'!A:A,MATCH($A6,'Existing Registry'!$A:$A,0))))

A6:BB6 are the columns in the Returned Registry that I want to see are TRUE or FALSE when matched against the data in the Existing Registry. The result needs to return the TRUE / FALSE (or 1/0 due to "--") for each column in the range, but using the formula which needs to act relative. ie A6 <> INDEX('Existing Registry'!A:A, then B6 <> INDEX('Existing Registry'!B:B etc

Will mock up a file if needed, but hoping the above just needs to be tweaked

Thanks in advance.

Hello Community, I have a block from which I can't get out....

I am using the formula sumaproduct to get the data of the sheet Absences with its first day and its last day, the problem I get when in the last day data there is no date, it is understood or it could be calculated with the formula today() to give you a data. The question is that when there is no date, the summaproduct that I have made adds up the total of days and even in a month that there is no initial or final date.

How can I solve it?

Thank you very much

I am using the formula sumaproduct to get the data of the sheet Absences with its first day and its last day, the problem I get when in the last day data there is no date, it is understood or it could be calculated with the formula today() to give you a data. The question is that when there is no date, the summaproduct that I have made adds up the total of days and even in a month that there is no initial or final date.

How can I solve it?

Thank you very much

Hi,

Is there any way to get the automated calendar months by the given dates if I but the date in Column A as 04-05-2018 and B as 03-05-2022 then I get the entire full months between these dates in the remaining columns starting from D

Is there any way to get the automated calendar months by the given dates if I but the date in Column A as 04-05-2018 and B as 03-05-2022 then I get the entire full months between these dates in the remaining columns starting from D

Hi All

Referring the table below, I wish to sum total invoice value on the line where invoice is ending. Each invoice number has line numbers and sum formula should be applicable only on last line/max line of each invoice. Kindly suggest.

Thanks

Sats

]]>Referring the table below, I wish to sum total invoice value on the line where invoice is ending. Each invoice number has line numbers and sum formula should be applicable only on last line/max line of each invoice. Kindly suggest.

Inv Number | Line No | Value | Sum by Invoice |

316 | 1 | $500 | |

316 | 2 | $215 | |

317 | 1 | $452 | |

317 | 2 | $125 | |

317 | 3 | $521 | |

317 | 4 | $124 | |

318 | 1 | $150 |

Thanks

Sats

Hi Guys,

Looking for some assistance here, I have been working on an automatic break generator and have so far come up with the attached using formulas and conditional formatting, however, some of the cells/times don't seem to be doing what I want them to do and I feel like I have gone too far to even imagine where to begin to look in order to fix them.

Essentially what I'm looking to accomplish is;

For example Agent 1 is working from 9am-5pm, so I set the start time and the end time of their shift, this automatically populates cells with a yellow bar highlighting the start and end time as well as calculates which breaks they are entitled to and then populates the breaks in green (15mins) and red (30mins).

Ideally the breaks would not be clashing where possible.

If someone can have a look and give me some advice it would be VERY much appreciated, this is a manual process we are having to do at work and is time consuming.Break Generator Test.xlsx

Looking for some assistance here, I have been working on an automatic break generator and have so far come up with the attached using formulas and conditional formatting, however, some of the cells/times don't seem to be doing what I want them to do and I feel like I have gone too far to even imagine where to begin to look in order to fix them.

Essentially what I'm looking to accomplish is;

For example Agent 1 is working from 9am-5pm, so I set the start time and the end time of their shift, this automatically populates cells with a yellow bar highlighting the start and end time as well as calculates which breaks they are entitled to and then populates the breaks in green (15mins) and red (30mins).

Ideally the breaks would not be clashing where possible.

If someone can have a look and give me some advice it would be VERY much appreciated, this is a manual process we are having to do at work and is time consuming.Break Generator Test.xlsx

I have city bills due every quarter so I went to create a date that will automatically change for their due date.

Fit example my first trash bill this year was due February 17, 2021 so the next due date was May 17, 2021 and after that August 17, 2021. Based on the current date May 4, 2021 it should say August 17, 2021..... And then automatically change after that based on whatever the current date is..,... Does that make sense?

Sent from my Pixel 5 using Tapatalk

]]>Fit example my first trash bill this year was due February 17, 2021 so the next due date was May 17, 2021 and after that August 17, 2021. Based on the current date May 4, 2021 it should say August 17, 2021..... And then automatically change after that based on whatever the current date is..,... Does that make sense?

Sent from my Pixel 5 using Tapatalk

Hi All,

I am trying to create a forumla that will tell me on my customer order tab that quality wants to review the order based on the product ID and customer. The customer name can vary (Usually by a customer having various addresses). So I want my spreadsheet to return a yes value if the cell contains the customer name and the product ID. I want to create a blank tab that our quality team can populate data into that will pull in the customer order tab. So my formula below works just fine for the cells that I have information entered into, but I want to have blank lines that quality can input data into. If I change the formula below to C$2:$C$500 and A$2:$A$500 then everything returns back a yes. Any idea on how I can modify this so that data can be entered in?

=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH('Quality List'!$C$2:$C$5,'Customer Orders'!F2)))>0,SUMPRODUCT(--ISNUMBER(SEARCH('Quality List'!$A$2:$A$5,'Customer Orders'!A2)))>0),"Yes","No")

I am trying to create a forumla that will tell me on my customer order tab that quality wants to review the order based on the product ID and customer. The customer name can vary (Usually by a customer having various addresses). So I want my spreadsheet to return a yes value if the cell contains the customer name and the product ID. I want to create a blank tab that our quality team can populate data into that will pull in the customer order tab. So my formula below works just fine for the cells that I have information entered into, but I want to have blank lines that quality can input data into. If I change the formula below to C$2:$C$500 and A$2:$A$500 then everything returns back a yes. Any idea on how I can modify this so that data can be entered in?

=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH('Quality List'!$C$2:$C$5,'Customer Orders'!F2)))>0,SUMPRODUCT(--ISNUMBER(SEARCH('Quality List'!$A$2:$A$5,'Customer Orders'!A2)))>0),"Yes","No")

=COUNTIF(A1:A347, "Alabama") Alaska

=COUNTIF(A1:A347, "Alabama") Arizona

=COUNTIF(A1:A347, "Alabama") Arkansas

=COUNTIF(A1:A347, "Alabama") California

How can I replace the "Alabama"s in the first column with "Alaska", "Arizona", "Arkansas" and "California" such that it becomes

=COUNTIF(A1:A347, "Alaska") Alaska

=COUNTIF(A1:A347, "Arizona") Arizona

=COUNTIF(A1:A347, "Arkansas") Arkansas

=COUNTIF(A1:A347, "California") California

I have tried posting this in other places without luck: https://www.excelforum.com/excel-for...nd-column.html, https://www.quora.com/unanswered/In-...he-next-column

=COUNTIF(A1:A347, "Alabama") Arizona

=COUNTIF(A1:A347, "Alabama") Arkansas

=COUNTIF(A1:A347, "Alabama") California

How can I replace the "Alabama"s in the first column with "Alaska", "Arizona", "Arkansas" and "California" such that it becomes

=COUNTIF(A1:A347, "Alaska") Alaska

=COUNTIF(A1:A347, "Arizona") Arizona

=COUNTIF(A1:A347, "Arkansas") Arkansas

=COUNTIF(A1:A347, "California") California

I have tried posting this in other places without luck: https://www.excelforum.com/excel-for...nd-column.html, https://www.quora.com/unanswered/In-...he-next-column