Need rules to tell me what to update

**Kazzaw** · 11-05-2021, 12:03 AM

I've attached a demo document to try and show what I am attempting to do

Information is updated by others into a spreadsheet on google docs and I copy/paste this information into Sheet1

I need something to search Sheet1 and Master CSV file sheets, and display what doesn't have an entry ("" indicates no entry) but has since been updated in the google sheets documentation

There are instructions included in the demo worksheet which should hopefully explain what I am trying to do.

The end formula needs to be able to be used across multiple sheets, I have just included 1 (Sheet1) for demo purposes

**KOKOSEK** · 11-05-2021, 04:27 AM

Is it something like this what you are looking for:

	A	B	C	D	E	F	G	H	I
1	Redacted information	Redacted Information	Redacted Information	Identifier	Misc Inf	Misc Inf	Misc Inf	Date	Unit
2	Redacted	Redacted	Redacted	1200052	50	Normal	7	2021/10/24 17:20	52
3	Redacted	Redacted	Redacted	1200097	50	Normal	4	2021/10/24 17:46	97
4	Redacted	Redacted	Redacted	1200099	50	Normal	8	2021/10/24 15:31	99
5	Redacted	Redacted	Redacted	1200127	50	Normal	1	2021/10/24 17:17	127
6	Redacted	Redacted	Redacted	1200175	50	Normal	6	2021/10/24 15:38	175
7	Redacted	Redacted	Redacted	1200231	50	Normal	9	2021/10/24 17:19	231
8	Redacted	Redacted	Redacted	1200341	50	Normal	13	2021/10/24 16:15	341
9	Redacted	Redacted	Redacted	1205335	50	Normal	2	2021/10/24 16:53	ID not found

Sheet: To Be Updated

	A	B	C	D	E	F	G	H	I
1	Redacted information	Redacted Information	Redacted Information	Identifier	Misc Inf	Misc Inf	Misc Inf	Date	Unit
2	Redacted	Redacted	Redacted	1200052	50	Normal	7	2021/10/24 17:20	=IFERROR(IF(D2<>"",VLOOKUP(""&D2,Sheet1!A:B,2,0),""),"ID not found")
3	Redacted	Redacted	Redacted	1200097	50	Normal	4	2021/10/24 17:46	=IFERROR(IF(D3<>"",VLOOKUP(""&D3,Sheet1!A:B,2,0),""),"ID not found")
4	Redacted	Redacted	Redacted	1200099	50	Normal	8	2021/10/24 15:31	=IFERROR(IF(D4<>"",VLOOKUP(""&D4,Sheet1!A:B,2,0),""),"ID not found")

Sheet: To Be Updated

Data from A to H are simple:

Formula:

Please Login or Register  to view this content.

copied into A2:H500

**Kazzaw** · 11-05-2021, 05:03 AM

No, I don't think thats what I'm after.

The 'Master CSV File' worksheet is a CSV type file my program records info in.
The 'Sheet1' worksheet is information I have copied from a google docs spreadsheet.

I am wanting 'To Be Updated' to compare the data in the 'Master CSV File' sheet to 'Sheet1'

If something in 'Master CSV File' has "", it means that that unit isn't identified.

Because multiple people are updating the google spreadsheet document, when I copy/paste from google docs into 'Sheet1' the newest information, I want 'To Be Updated' to show me what is missing in the 'Master CSV File' so I can make the additions

**AliGW** · 11-05-2021, 06:14 AM

I think you can stop copying and pasting and use PowerQuery to import the data:

https://www.youtube.com/watch?v=OIGv2y9eeFg

You can then also use PowerQuery to update the master data.

**Kazzaw** · 11-05-2021, 11:21 PM

Thanks.

Power Query might be more of a hindrance than a help, I find it easier to copy/paste each sheet across. But I did try it and it might come in use for one of my other projects

My issue still stands where I need to flag what has been updated in the google sheets when compared to my master data sheet.

**KOKOSEK** · 11-06-2021, 09:25 PM

So, on To be updated you wanna see only lines which 'unit' value has been place, as on Sheet1 (paste from external source).

**Kazzaw** · 11-06-2021, 11:05 PM

Yes. Master CSV file contains what I currently have. Sheet1 contains the latest information thats been consolidated. To be updated just needs to flag what I dont have in the master CSV file (indicated by "") so I can update it.

**KOKOSEK** · 11-08-2021, 11:47 AM

Try this:

Please Login or Register  to view this content.

Check attached file.

**DJunqueira** · 11-08-2021, 06:51 PM

I would easily use PQ to solve this.

**Kazzaw** · 11-09-2021, 12:43 AM

Originally Posted by KOKOSEK

Try this:

Please Login or Register  to view this content.

Check attached file.

I think thats close. I loaded my actual dataset into this, but it appears to list all of them.

I'm thinking this could be an error on my part - All entries in the Master CSV File in column I contain "" - Per my example, I had incorrectly entered Unit and then just numbers. These numbers should have "52", "97" etc. "" is blank.

I hope this makes sense

**KOKOSEK** · 11-09-2021, 05:03 AM

So, you want to FULL LIST from Master File, but updated with values from Sheet1 (if exists for particular ID).
I've asked on post #6: "on To be updated you wanna see only lines which 'unit' value has been place, as on Sheet1" you said Yes.

Try this then:

Please Login or Register  to view this content.

**Kazzaw** · 11-09-2021, 05:24 AM

That is not what I am after. I'll try explain again using the original worksheet I attached.

In Master CSV file, I have the identifier in column D and the unit in column I

In Sheet1, I have the Identifier in column A and the unit in column B

I want To Be Updated to show me where an identifier exists in column A from Sheet1 that matches Column D from Master CSV file, where the Master CSV File currently has "" (indicates blank/no unit recorded)

In the example on my original attached, I have Row 5 from Master CSV File showing that the identifier 1200127 (Column D) doesn't have a unit name attached - As indicated by Column I displaying ""

In Sheet1, the identifier (Column A) 1200127 matches with the unit 127. As 1200127 (column D) in the Master CSV File shows as blank, it should display in the To Be Updated sheet that I can now fill this information in.

In the To Be Updated, I only want to show the items that need to be updated, so that I can go back into the Master CSV File sheet and update them accordingly.

I hope that makes it clearer

I've attached a new version of my original worksheet to adjust for the formatting error I made in the Master CSV File sheet (Units have a " around each side of their entry, no other changes made)

**KOKOSEK** · 11-09-2021, 05:39 AM

Originally Posted by Kazzaw

In the To Be Updated, I only want to show the items that need to be updated, so that I can go back into the Master CSV File sheet and update them accordingly.

So why something wrong with code from post #8?
Maybe you should more clearly explain/show how To be updated sheet should be after process.

Try this then:

Please Login or Register  to view this content.

p.s. maybe my english is not good enough and I still do not understand what you exactly want.

**Kazzaw** · 11-09-2021, 09:22 PM

I've tried entering that however nothing appears when the Check Updates button is pressed

**Kazzaw** · 11-11-2021, 10:53 PM

I've attached some additional examples for clarity

**KOKOSEK** · 11-12-2021, 04:13 AM

Please Login or Register  to view this content.

On top your expected results, on bottom results
3.JPG

Need rules to tell me what to update

LinkBack

Thread Tools

Rate This Thread

Display

Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Re: Need rules to tell me what to update

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] I would like to establish some rules in the result column of my spreadsheet. The rules a

Update folder for all rules to a new folder

conditional formatting rules for a cell on sheet 1 based on rules from sheet 2

[SOLVED] Outlook 2010 - Creating Rules - Rules Constant

Improvement: update the forum rules

help with IF rules

Bookmarks

Bookmarks

Posting Permissions