Organizing Data with Excel

Hello,

I am looking for a way to create a spreadsheet that can be used as a template for organizing data. This spreadsheet needs to be able to receive data and add two columns (which can be done with a macro) and also rearrange the same columns every time. I would like this spreadsheet to be able to fill in blanks of a new column based on information given in another column. How would I go about doing this?

Also, is there a way for it to read data from one column and if it is within a certain range, then output a specific value in a new column?

Any help is greatly appreciated!

Thanks.

A little more detail now that the Before and After spreadsheets are uploaded. I am looking for a way to get from the before to the after. I want to be able to paste data in a spreadsheet and it makes it look like the After spreadsheet either in a new document (if possible) or at least a new sheet.

To get to the after from the before, the following steps take place:
-Size and Product columns are inserted
-The columns are rearranged to be in the order: user status, TechIdentNo.,Size,Product,ConstructYear,Description,Equipment,Manufacturer,Start-up date, AP Design option (the rest stay in same order)
-Anything in the TechIdenNo. column that contains a "LH" gets "Hydrogen" in the Product column
-Anything in the TechIdenNo. column that contains a "T" gets deleted from the data
-The remaining in TechIdenNo. get "Atmospheric" in the Product column
-From the water volume column, the size column gets populated using this table as a reference:
0-1000 G = 500 G
1,001 G – 2,000 G = 1,500 G
2,001 G – 4,000 G = 3,000 G
4,001 G – 8,000 G = 6,000 G
8,001 G – 10,000 G = 9,000 G
10,001 G – 12,000 G = 11,000 G
12,001 G – 14,000 G = 13,000 G
14,001 – 20,000 G = 15,000 G
> 15,000 G

for example...if the water volume is 9,000 G then "9000" would go in the size column
if the water volume is 12,500 then "13,000" would go in the size column (omit G from size column)


These are the steps I would like it to perform. Is this possible? Thanks so much for the help!

Thank you for the help! I am getting an error when I try and run the Macro "Run-time error '9':
Subscript out of range

I cut out a lot of the data when I posted the Before and After spreadsheet for simplicity. If you need me to post one with more information in it please let me know!

Last request I have on this code. Is there a way it could automatically auto fit the columns to fit the contents? Thanks!!!

Whenever the AFTER spreadsheet opens, there is an error I am getting with this line:
V = Left(wa.Cells(i, 29), InStrRev(wa.Cells(i, 29), ".") + 2)

Can this If statement be modified so that if the water volume column is blank, then it prints "Not available" or something in the Size column. I think it is having trouble interpreting the blank in the water volume column.

Whenever the AFTER spreadsheet opens, there is an error I am getting with this line:
V = Left(wa.Cells(i, 29), InStrRev(wa.Cells(i, 29), ".") + 2)

Can this If statement be modified so that if the water volume column is blank, then it prints "Not available" or something in the Size column. I think it is having trouble interpreting the blank in the water volume column.

I don't think the code is deleting items with a "T" in them from the TechidentNo. so this might be causing some of the problem?

It looks great!! Some of the items with a "T" under the TechIdentNo. column are showing as Atmospheric though. (Row 692 and 693 for example). Any idea why this might be happening or any thoughts on how to fix? Also, can the rows containing "TK" also be removed from this column? if we can get those two issues figured out it will be perfect!

When the new document is presented, is there a quick way that the columns can already have filters put on them? I can't thank you enough for looking into this with me.

I believe this works! I will let you know if I stumble upon any issues. Again, I can't thank you enough for all of your help! I might have some minor macro Excel questions in the future that I will probably ask you if that is alright.

Hey thank you so much for looking at this! Nothing happened when I ran this macro though.

I spoke too soon...this is fantastic! Thank you!

Is there a simple add in that can be used to make the rows the same size as they are on the first sheet? Other than that this looks great and I really appreciate it!

Is it possible for the items to have the exact same size as they do on Sheet 1 or do they have to be auto-fitted and preset with a specific row height? If this is the way it has to be that is totally okay and I want to thank you very much for your help again. This will work great!

Hello,

I have attached the code that I have been using, but I am wondering if I could change something. Right now it is set up to delete everything in one column that has a "T". I was wondering if this can be modified so that now everything with a "T" gets "CO2" put into the "Product" column that we have getting inserted.


Option Explicit
Sub Organizer(): Dim wb As Worksheet, wa As Worksheet, r As Long, i As Long, V As Long
Set wb = Workbooks("Organize.xlsm").Sheets("Sheet1")
r = wb.Range("A" & Rows.Count).End(xlUp).Row
Workbooks.Add: ActiveWorkbook.SaveAs wb.Parent.Path & "\" & "DATE IDLE TANKS.xlsx"
Set wa = Workbooks("DATE IDLE TANKS.xlsx").Sheets("Sheet1")

wb.Cells.Copy: wa.Range("A1").PasteSpecial xlPasteFormats
wa.Range("Z1").Resize(1, 2).Copy
wa.Range("AB1").PasteSpecial xlPasteFormats

wa.Range("A1").Resize(r, 1).Value = wb.Range("A1").Resize(r, 1).Value
wa.Range("C1") = "SIZE": wa.Range("D1") = "PRODUCT"
wa.Range("B1").Resize(r, 1).Value = wb.Range("G1").Resize(r, 1).Value
wa.Range("E1").Resize(r, 5).Value = wb.Range("B1").Resize(r, 5).Value
wa.Range("J1").Resize(r, 20).Value = wb.Range("H1").Resize(r, 20).Value

For i = 2 To r
If wa.Cells(i, 2) <> "" Then wa.Cells(i, 4) = "Atmospheric"
If InStr(1, wa.Cells(i, 2), "T") Then
wa.Rows(i).Delete Shift:=xlUp: i = i - 1: GoTo GetNext: End If
If InStr(1, wa.Cells(i, 2), "LH") Then wa.Cells(i, 4) = "Hydrogen"

If InStr(1, wa.Cells(i, 29), ".") Then
V = Left(wa.Cells(i, 29), InStrRev(wa.Cells(i, 29), ".") + 2)
If V > 20000 Then
V = V
ElseIf V > 14000 Then
V = 15000
ElseIf V > 12000 Then
V = 13000
ElseIf V > 10000 Then
V = 11000
ElseIf V > 8000 Then
V = 9000
ElseIf V > 4000 Then
V = 6000
ElseIf V > 2000 Then
V = 3000
ElseIf V > 1000 Then
V = 1500
Else
V = 500
End If
wa.Cells(i, 3) = V
End If
GetNext: Next i
wa.Columns.AutoFit: wa.Columns.AutoFilter
End Sub

Hey xladept,

I am looking for a way to utilize this code, however the data is being brought into the spreadsheet in a new order and I am not sure how to get the PRODUCT and SIZE columns to adjust to these changes. Would you be able to modify the code so that it is able to do the same thing as before, but with the columns in this order? Thank you so much!