How to combine products with the same name in 1?

Question

There is a .csv file with a large list of products. Some goods are grouped one after another and differ in one field (in fact, they are one and the same product, which has different properties).

ID NAME PRICE DESC 3630 лодка Адмирал 280 80.0000 св 1 3630 лодка Адмирал 280 80.0000 св 2 3630 лодка Адмирал 280 80.0000 св 3

How to combine such products into one, writing down all the different fields in one form: св 1;св 2;св 3 ?

 ID NAME PRICE DESC 3630 лодка Адмирал 280 80.0000 св 1;св 2;св 3

I try to write in a column like this =ЕСЛИ(A2=A1;B101894&"; "&X2;X2) and multiply to all the lines, but I get a warning about a circular reference .. Why?

write a simpler macro in vba, which will compare strings by the similarity of the first lines and the divergence of the last.
@Denis I am not familiar with macros - tell me, plz, if you know how
If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer).

Accepted Answer · 2016-03-25T00:30:09

The desired result can be achieved in two stages:

We combine (group) products by the NAME field by creating a pivot table

Using the MergeIfUniq function, we combine fields with a description

Final result

The code of the MergeIfUniq function is taken from http://www.planetaexcel.ru/techniques/7/205/ and a condition is added that eliminates duplication of fields with descriptions

 Function MergeIfUniq (TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " 'символы-разделители (можно заменить на пробел или ; и т.д.) 'если диапазоны проверки и склеивания не равны друг другу - выходим с ошибкой If SearchRange.Count <> TextRange.Count Then MergeIfUniq = CVErr(xlErrRef) Exit Function End If 'проходим по все ячейкам, проверяем условие и собираем текст в переменную OutText 'исключаем дублирование в переменной OutText повторящихся значений ячеек For i = 1 To SearchRange.Cells.Count If (SearchRange.Cells(i) Like Condition) And _ (InStr(1, OutText, TextRange.Cells(i), vbTextCompare) = 0) Then _ OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'выводим результаты без последнего разделителя MergeIfUniq = Left(OutText, Len(OutText) - Len(Delimeter)) End Function

Ps. Tested on the following data

 ID Name Price Desk 3630 лодка Адмирал 280 800 000 св 2 3630 лодка Адмирал 280 800 000 св 3 3631 лодка Тайфун 700 000 св 1 3631 лодка Тайфун 700 000 св 2 3631 лодка Тайфун 700 000 св 3 3631 лодка Тайфун 700 000 св 4 3631 лодка Тайфун 700 000 св 5 3620 лодка Оптимист 400 000 св 99 3620 лодка Оптимист 400 000 св 91 3620 лодка Оптимист 400 000 св 92 3631 лодка Тайфун 700 000 св 5 3631 лодка Тайфун 700 000 св 5

here it is customary to give a complete answer, and references are given only as a supplement / illustration.

Dmitry Shelemeh Dmitry Shelemeh one 1 bronze sign · Answer 2 · 2016-01-29T12:20:13

Hey. Here's what I got:

  Sub Макрос() start: For Each element In Range("a2:a8") If element.Value = "" Then Exit Sub ElseIf element.Value = element.Offset(1, 0) Then tmp = element.Offset(0, 3).Value & "; " & element.Offset(1, 3) element.Offset(0, 3).Value = tmp Rows(element.Row + 1).Select Selection.Delete GoTo start Else End If Next element End Sub

If necessary, I can lay out a file with an example.

How to combine products with the same name in 1?

2 answers 2

More articles: