Excel work sheets

[harry234]

Not even sure I’m asking correctly

I have 2 sheets. 1 is called “Working”, whose data is collected from the folders menu. Using the “copy path” function and pasting the results to excel “working” sheet. (This is done because the file folder is continuously updated by files downloaded from the internet.)

Sheet 2 is an inventory of those files and has line item data associated with each line item.

What I would like to do is update the inventory sheet using the working sheet but keep all inventory data associated with respective line items.

Example: Inventory sheet

	Item	Size	Location 1
1​	100 RTV Silicone Industrial
2​	880 Crown Chassis Grease (Texas Refinery Corp)	14,1oz Tube	EL2

Example: Working sheet

1​	100 RTV Silicone Industrial
2​	880 Crown Chassis Grease
3​	Ace plumbers epoxy putty

If I download a file called, Say “123 Battery”, my working sheet will look like:

1​	100 RTV Silicone Industrial
	123A Battery
2​	880 Crown Chassis Grease
3​	Ace plumbers epoxy putty

The objective would be to insert “123 Battery” in the alfa numerically correct line and keep relevant information attached to the line that was moved down. (Example Inventory Sheet below)

	Item	Size	Location 1
1​	100 RTV Silicone Industrial	8oz can	Tool
2​	123 Battery
3​	880 Crown Chassis Grease (Texas Refinery Corp)	14.1oz Tube	EL2
4​	Ace plumbers epoxy putty	20oz stick	Control

 

[jolivanes]

You can add any line anywhere and sort it, can't you?
Add the numbers, if required, after, with something like

With Range("A1:A" & Cells(Rows.Count, 2).End(xlUp).Row)
.Formula = "=ROW()"
.Value = .Value
End With

or any snippet that will add the numbers.

 

[harry234]

That could work, but Inventory sheet has 110 lines and Work sheet has 120 lines. Wouldn't I have to write code for each line?

 

[jolivanes]

Could you explain what you want to do. The way I understand it is that you get all the file names without extension in a sheet and copy some over to another sheet.
Can't you just import the file names you want, paste these at the bottom of your sheet, add the info for size and location and then sort everything?

 

[harry234]

Sure, I'll try. I'm using data from a file folder as an inventory list. Since I don't update the inventory every time I down load product information (usually update once a month), I copy the entire content of the file folder to path and paste it to a worksheet. Currenty I compair the worksheet to the inventory list, add a row where a new product should appear alphabetically and copy/paste the new data in. This takes a lot of time. So, I guess what I'm trying to do is compair the worksheet to the inventory sheet and add items that are not on the inventory sheet automatically. Does that make sense?

 

[jolivanes]

Re: "I'm using data from a file folder as an inventory list."
A folder usually has many files in it.
Do you mean "from a file in a specific folder"?

Re: "copy the entire content of the file folder to path"
You have to elaborate on this as I don't know what that means.

It just about sounds like you want to compare data in two sheets and data missing in one sheet should be copied into the sheet where it is missing.
If that is the case, we need to know if both workbooks that have the sheets with data are open, what the names of these workbooks are, what the names of the sheets with the data are and in which columns the data is in these sheets.

 

[harry234]

File Folder

Copied into this

And then I want to populate this

Thanks

 

[jolivanes]

If you would write in words what you're doing, what would that be?

 

[harry234]

Re: "I'm using data from a file folder as an inventory list."
A folder usually has many files in it.
Do you mean "from a file in a specific folder"? YES

Re: "copy the entire content of the file folder to path"
You have to elaborate on this as I don't know what that means. When copying the content of a folder you can only copy the entire path of that folder, i.e.
"C:\Desktop\Lot 1&2 SDS Program\Lot 1 SDS File\100 RTV Silicone Industrial.pdf"
I will copy the entire content of that folder. (120 files) because they are in alphabetical order.
I then copy these files into a work sheet called working... Then use the "Replace" function in excel to remove the path "C:\Desktop\Lot 1&2 SDS Program\Lot 1 SDS File\" (and) ".pdf"
Leaving me just the file name. "100 RTV Silicone Industrial" (120 file names)

At that point is where may question starts. I have another sheet named "Inventory", this sheet contains various info for ordering, locations, quantities, etc.
What I would like to do is compare the 2 worksheets, and insert any row that is missing in the "Inventory" Worksheet...

Currently I do not have the "Spreadsheet Inquire add-in" and am looking for another solution...

As to your last question: It just about sounds like you want to compare data in two sheets and data missing in one sheet should be copied into the sheet where it is missing.(Yes) that’s exactly what I want to do. If that is the case, we need to know if both workbooks that have the sheets with data are open (is one workbook with both worksheets) , what the names of these workbooks are, what the names of the sheets (Sheet Names are literally “Inventory” and “Working”) with the data are and in which columns the data is in these sheets. (In “Inventory” the column “B” named “Item”, and in “Working” the column “B” named Item.) The “Inventory” sheet currently has 112 rows, The Columns go from “A” through “AB”. The “Working” sheet has 120 rows and 2 columns, A and B.

I hope this sheds some light, I don’t know how else to explain it

 

[jolivanes]

Yes, It does. Good explanation. Thanks for that.
Your solution should be here momentarily.