Updating new content alphabetically in the main file and the

vladimiratanasiu

Board Regular
Joined
Dec 17, 2010
Messages
228
Office Version
  1. 2019
Platform
  1. Windows
Hello!

This problem is somehow alike my previous post, but with different results. I have a workbook including more files with data about some companies, as shown in the next Dropbox link : 1. Lista Furnizori Agreati.xlsx . The file "Furnizori" includes all companies, arranged alphabetically. Each other file (A,B,C etc.) includes only the the group of companies from file "Furnizori", whose names start with the alphabet letters (A, B, C etc.). At one time, one / more rows with companies could be added / deleted in a file. My question is how could be automatically updated the content from the file "Furnizori", when are made changes in other files, adding or removing new / existing companies and auto arrange them alphabetically. Similarly, if a company is added / deleted from the file "Furnizori", I need to be operated the same changes in the file where the company/ies is/are placed. At the same time, depending of the changes, all elements should auto arrange alphabetically also in the main file and in the letter-marked one(s).

Thank you in advance.
 

Attachments

  • Untitled3.png
    Untitled3.png
    126.5 KB · Views: 9
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,299
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
In order to do what you want, each row in the "Furnizori" sheet must have a unique identifier which must also be included in column A of all the 'company' sheets. You currently have unique values in column A of the "Furnizori" sheet which could be used as identifiers, however, these values are not included in column A of the 'company' sheets. Could you update your file to include the unique identifiers in column A of all the 'company' sheets and attach a link to the revised file?
 

vladimiratanasiu

Board Regular
Joined
Dec 17, 2010
Messages
228
Office Version
  1. 2019
Platform
  1. Windows
In order to do what you want, each row in the "Furnizori" sheet must have a unique identifier which must also be included in column A of all the 'company' sheets. You currently have unique values in column A of the "Furnizori" sheet which could be used as identifiers, however, these values are not included in column A of the 'company' sheets. Could you update your file to include the unique identifiers in column A of all the 'company' sheets and attach a link to the revised file?
Hello!

I added the necessary identifiers in the column A from all alphabetical sheets as shown in the linked file 1. Lista Furnizori Agreati .xlsx. Anyway, the number of rows from the main / alphabetic files and their present order are just indicative, taking into account that the firms will vary in the future.
 

vladimiratanasiu

Board Regular
Joined
Dec 17, 2010
Messages
228
Office Version
  1. 2019
Platform
  1. Windows
In order to do what you want, each row in the "Furnizori" sheet must have a unique identifier which must also be included in column A of all the 'company' sheets. You currently have unique values in column A of the "Furnizori" sheet which could be used as identifiers, however, these values are not included in column A of the 'company' sheets. Could you update your file to include the unique identifiers in column A of all the 'company' sheets and attach a link to the revised file?
If you referred to the unique identifier(s), allocated in the main list for each company, I linked in 1. Lista Furnizori Agreati - grouped .xlsx a table with every alphabetical sections containing exactly the original numbers.
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,299
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
The 1. Lista Furnizori Agreati - grouped .xlsx file is the desired format. We have to take this one step at a time.

When you add a new company to any of the individual company sheets, do you enter data in all the columns starting from column A to L? When you delete a company from any of those sheets, do you delete the whole row or just the supplier name in column B?

When you add a new company in the "Furnizori(suppliers)" sheet, do you also enter data in column C? When you delete a company from the "Furnizori(suppliers)" sheet, do you delete the whole row or just the supplier name in column B?
 

vladimiratanasiu

Board Regular
Joined
Dec 17, 2010
Messages
228
Office Version
  1. 2019
Platform
  1. Windows
The 1. Lista Furnizori Agreati - grouped .xlsx file is the desired format. We have to take this one step at a time.

When you add a new company to any of the individual company sheets, do you enter data in all the columns starting from column A to L? When you delete a company from any of those sheets, do you delete the whole row or just the supplier name in column B?

When you add a new company in the "Furnizori(suppliers)" sheet, do you also enter data in column C? When you delete a company from the "Furnizori(suppliers)" sheet, do you delete the whole row or just the supplier name in column B?
I'm trying to answer punctually:

1. I use constantly the columns B (Supplier name) and H (Purchased products), that must always be filled in. Data from the other columns is inserted only if necessary to, complying with no certain criteria (see the attached photo).
2. In the sheet "Furnizori(suppliers)" I enter data also in the column C, in case of processing information about a new company .
3. If I need to remove a company, I delete the whole row where it's placed.
4. I rectified the title of column C from files A-Z, in order to avoid possible misunderstandings. It abbreviated the Romanian expression "Number of contract", as shown in the linked table 1. Lista Furnizori Agreati - grouped .xlsx.
5. I separated the large group of firms in specific alphabetical subgroups, linked with their original identifier (Nr. crt.). Anyway, neither of them won't keep the present numbering in the future, considering the certain modifications of the content. Could you take into account a dynamic system to adjust the the whole and the alphabetical numbering lists, in order to exclude overlapped / missing Crt. numbers?

Thank you!
 

Attachments

  • Untitled3.png
    Untitled3.png
    32.6 KB · Views: 9

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,299
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Will the supplier names in column B of the "Furnizori(suppliers)" sheet always be unique without any duplicates? If so, we can use those names as the unique identifier instead of the Nr.crt in column A. I'm going to be fairly busy in the next few days so I may not be able to respond quickly.
 

vladimiratanasiu

Board Regular
Joined
Dec 17, 2010
Messages
228
Office Version
  1. 2019
Platform
  1. Windows
Will the supplier names in column B of the "Furnizori(suppliers)" sheet always be unique without any duplicates? If so, we can use those names as the unique identifier instead of the Nr.crt in column A. I'm going to be fairly busy in the next few days so I may not be able to respond quickly.
Yes, they will always be unique. I appreciate a lot your voluntary support, that's why the time you answer is not a problem.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,299
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Click here to download your file. You must make sure that you have data in column H of all the company sheets. I have entered some dummy data in A and B. When you want to delete a supplier, instead of deleting the entire row, just click on the name in column B and use the DELETE key to remove it. This code is in the code module for ThisWorkbook. Play with it and let me know how it works out.
VBA Code:
Dim oldVal As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Target.Column
        Case Is = 2
            oldVal = Target
        Case Is = 3
            oldVal = Target.Offset(, -1)
    End Select
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.ScreenUpdating = False
    Dim SN As Range, wsCO As String, desWS As Worksheet
    Set desWS = Sheets("Furnizori(suppliers)")
    If ActiveSheet.Name <> "Furnizori(suppliers)" And ActiveSheet.Name <> "LEGUME" Then
        If Target.CountLarge > 1 Then Exit Sub
        Select Case Target.Column
            Case Is = 2
                If Target = "" Then
                    Set SN = desWS.Range("B:B").Find(oldVal, LookIn:=xlValues, lookat:=xlWhole)
                    If Not SN Is Nothing Then
                        SN.EntireRow.Delete
                        With desWS
                            .Range("A2").Value = "1"
                            .Range("A2").AutoFill Destination:=.Range("A2").Resize(.Range("B" & Rows.Count).End(xlUp).Row - 1, 1), Type:=xlFillSeries
                        End With
                        Target.EntireRow.Delete
                        With ActiveSheet
                            .Range("A2").Value = "1"
                            .Range("A2").AutoFill Destination:=.Range("A2").Resize(.Range("B" & Rows.Count).End(xlUp).Row - 1, 1), Type:=xlFillSeries
                        End With
                    End If
                End If
            Case Is = 8
                Set SN = desWS.Range("B:B").Find(Target.Offset(, -6).Value, LookIn:=xlValues, lookat:=xlWhole)
                If SN Is Nothing Then
                    With desWS
                        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value + 1
                        .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(, 2).Value = Array(Target.Offset(, -6), Target)
                        .Cells(1, 1).Sort Key1:=.Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
                        .Range("A2").Value = "1"
                        .Range("A2").AutoFill Destination:=.Range("A2").Resize(.Range("B" & Rows.Count).End(xlUp).Row - 1, 1), Type:=xlFillSeries
                    End With
                    With ActiveSheet
                        .Range("A2").Value = "1"
                        .Range("A2").AutoFill Destination:=.Range("A2").Resize(.Range("B" & Rows.Count).End(xlUp).Row - 1, 1), Type:=xlFillSeries
                    End With
                End If
        End Select
    ElseIf ActiveSheet.Name = "Furnizori(suppliers)" Then
        If Target.CountLarge > 1 Then Exit Sub
        wsCO = Left(oldVal, 1)
        Select Case Target.Column
            Case Is = 2
                If Target = "" Then
                    Set SN = Sheets(wsCO).Range("B:B").Find(oldVal, LookIn:=xlValues, lookat:=xlWhole)
                    If Not SN Is Nothing Then
                        SN.EntireRow.Delete
                        With Sheets(wsCO)
                            .Range("A2").Value = "1"
                            .Range("A2").AutoFill Destination:=.Range("A2").Resize(.Range("B" & Rows.Count).End(xlUp).Row - 1, 1), Type:=xlFillSeries
                        End With
                        Target.EntireRow.Delete
                        With ActiveSheet
                            .Range("A2").Value = "1"
                            .Range("A2").AutoFill Destination:=.Range("A2").Resize(.Range("B" & Rows.Count).End(xlUp).Row - 1, 1), Type:=xlFillSeries
                        End With
                    End If
                End If
            Case Is = 3
                Set SN = Sheets(wsCO).Range("B:B").Find(Target.Offset(, -1).Value, LookIn:=xlValues, lookat:=xlWhole)
                If SN Is Nothing Then
                    With Sheets(wsCO)
                        '.Cells(.Rows.Count, "A").End(xlUp).Offset(1) = Target.Offset(, -2)
                        .Cells(.Rows.Count, "B").End(xlUp).Offset(1) = Target.Offset(, -1)
                        .Cells(.Rows.Count, "H").End(xlUp).Offset(1) = Target
                        .Cells(1, 1).Sort Key1:=.Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
                        .Range("A2").Value = "1"
                        .Range("A2").AutoFill Destination:=.Range("A2").Resize(.Range("B" & Rows.Count).End(xlUp).Row - 1, 1), Type:=xlFillSeries
                    End With
                    With desWS
                        .Cells(1, 1).Sort Key1:=.Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
                        .Range("A2").Value = "1"
                        .Range("A2").AutoFill Destination:=.Range("A2").Resize(.Range("B" & Rows.Count).End(xlUp).Row - 1, 1), Type:=xlFillSeries
                    End With
                End If
        End Select
    End If
    Application.ScreenUpdating = True
End Sub
 
Solution

vladimiratanasiu

Board Regular
Joined
Dec 17, 2010
Messages
228
Office Version
  1. 2019
Platform
  1. Windows
You must make sure that you have data in column H of all the company sheets. I have entered some dummy data in A and B. When you want to delete a supplier, instead of deleting the entire row, just click on the name in column B and use the DELETE key to remove it.
Thank you very much!:):):) It works great, matching exactly the conditions I need.
 

Forum statistics

Threads
1,181,633
Messages
5,931,105
Members
436,775
Latest member
Taproot007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top