Jun 28 2022 07:36 AM
Hi,
Does anyone know if it is possible to do this? I have two spreadsheets containing data from two consecutive months. In the earlier spreadsheet, let's say I have a list of customers colour coded by type, with some information about them in a few cells along the row, and then a column for comments at the end. In the second spreadsheet I have a new list of customers with some information on each, which will contain many of the same customers, but in a different order, not colour coded and without any comments. I want to update the second spreadsheet so that all customers which are also in the first spreadsheet get colour coded the same way as they are in that, and I want the relevant comment to appear in the comments column for that customer in the new spreadsheet. Then I only need to update the new customers rather than going through the old customers manually.
So I need every cell in column B of the new spreadsheet to search through column B of the old spreadsheet looking for an identical match. If it finds it, I need it to fill the cell with the same colour as that cell in the old spreadsheet, and then I need it to add the comment from column M in the old spreadsheet to column M of the new spreadsheet in the same row as the cell it is colour coding.
Thanks a lot if anyone is able to help!
Jun 28 2022 08:13 AM
Are the customers on the first sheet color-coded manually or using conditional formatting?
To copy the comment, you can use an IFERROR/VLOOKUP combination, or XLOOKUP if you have Microsoft 365 or Office 2021.
Jun 28 2022 08:15 AM
I suspect it is indeed possible, probably fairly easy. HOWEVER, it's also likely that a wholly different approach might even be better.
Is it possible for you to post a copy--just without real names and other identifiers--of your two spreadsheets? Use OneDrive or GoogleDrive if you aren't able to post them here in this forum. Then post a link to them.
Jun 28 2022 08:16 AM
Sub color()
Dim i As Integer
Dim j As Integer
Dim company As Range
Set company = Tabelle1.Range("B2:B10000")
For i = 2 To 26
j = Application.WorksheetFunction.Match(Tabelle2.Cells(i, 2), company, 0)
Tabelle2.Cells(i, 13).Interior.ColorIndex = Tabelle1.Cells(j + 1, 2).Interior.ColorIndex
Tabelle2.Cells(i, 13).Value = Tabelle1.Cells(j + 1, 13).Value
Next i
End Sub
Maybe with this code. In the attached file you can click the button in cell G2 to start the macro.
Jun 28 2022 08:33 AM
Jun 28 2022 08:45 AM
Jun 28 2022 08:49 AM
Jun 28 2022 09:41 AM
Jun 28 2022 10:49 AM
@mathetes All very good points but sadly in this case the colour coding is done based on qualitative analysis of things not contained on the spreadsheet so I don't think conditional formatting could be used. I'm not sure how easy it'll be to anonymise the spreadsheet and post it but I'll have a look at doing it later. Thanks!
Jun 28 2022 03:02 PM