Formula to move a cell from one sheet to another based on conditions
Hi all,
I'm currently using Zapier to move data from a google drive to a data source Smartsheet. During the transfer, sometimes there are multiple rows with the same claim numbers on the data source sheet (that part isn't going to change unfortunately). I have a separate summary sheet that that pulls the information I need from the data source sheet using an match/index function using the claim number to match.
On my summary sheet, I'm hoping to have some kind of automation or formula that will only pull over new claim numbers that does not exist on the summary sheet. Once the new claim number is on the summary sheet, the functions automatically calculates the data I need.
I don't know if this will help, but here's the summary sheet
Here's the data source screen shot.
Answers
-
Though I did not exactly "move" a cell from one sheet to another based on conditions, I figured out a way to get a new "claimNum" from another sheet to the Summary Sheet.😅
The method uses the INDEX(range, MATCH(search_value, range)) method.
The range is the claimNum column of another sheet.
To get row_index, I compared the ranking of the blank cells of the ExisingCalimNum column and the ranking number of another sheet's newly added claimNum's number part.
As the MATCH's search_value, I added a new column, ID, or row ID and ranked the ID whose row's ExsintingClaimNum value is blank.
So, for example, the first row with blank ExsintingClaimNum is 1, and the second is 2.
As the MATCH's range, I added the "New claimNum Rank" column in another sheet that gets data from Google Sheets.
The "New claimNum Rank" gets the ranking number of the new claimNum's number part. (number part because RANKEQ() ranks number)
If you examine the demo dashboard, you will understand what I mean.
-
I attempted this with no luck. Also, I don't fully understand the logic, which might by way I was unsuccessful.
@Paul Newcome do you have thoughts on this? you've been great at explaining this to me at a beginner level
-
@jmyzk_cloudsmart_jp - this solution actually helps me with a problem I'm working on. In this example, can you explain how the ExistingClaimNum column in the Summary Sheet is populated?
-
Hi @l.gann
I copied 6 data from the ClaimNum column of murphyspccms' summary sheet image as examples of existing ClaimNum and pasted them into ExistingClaimNum on my summary sheet.
FYI, to copy the numbers from the image, I used the following free tool from Blandfolder
-
@murphyspccms Is the only reason you are trying to move this stuff over is to filter out old duplicates? If so, we can use a formula to convert a date/time stamp into a useable number and then another formula to check the box for the most recent (or first if you prefer) entry for each claim number. Then a filter on the sheet or a report based on this checkbox will give you what you need.
Help Article Resources
Categories
Check out the Formula Handbook template!
