Need help Arranging/Filtering Data

[desibouy]

Hey,

So we're using a new system (shopify) and I've been struggling with Data Imports for a while now. Reason because Shopify uses Variant Products (Size Color etc), So the Parent Item (the one where the Variants are stored under) needs to be labelled "Top Row" in a separate Column (usually I don't use this) Shopify then uses this along with Handle to import the Data.

I have created many files and each one has pretty much failed. I've tried using filters but they don't work so having to manually fix these.

This is an example of the issue

Incontinence Category_UPDATED2022_TP.xlsx
	A	B	C	D	E
1	Variant Metafield: Unique-Internal-ID.Unique_Internal_ID [string]	Variant Metafield: Product-Family-ID.Product_Family_ID [string]	Handle	Title	Top Row
2	82650	82644	Attends_Extended_Wear_Brief
3	82649	82644	Attends_Extended_Wear_Brief
4	82648	82644	Attends_Extended_Wear_Brief
5	82647	82644	Attends_Extended_Wear_Brief
6	82646	82644	Attends_Extended_Wear_Brief	Attends Extended Wear Brief	TRUE
7	82645	82644	Attends_Extended_Wear_Brief
8	1659	1655	Cardinal_Protective_Underwear	Cardinal Protective Underwear	TRUE
9	1658	1655	Cardinal_Protective_Underwear
10	1656	1655	Cardinal_Protective_Underwear
11	2216	2207	Nu-Fit_Briefs
12	2215	2207	Nu-Fit_Briefs
13	2213	2207	Nu-Fit_Briefs
14	2212	2207	Nu-Fit_Briefs	Nu-Fit Briefs	TRUE
15	2211	2207	Nu-Fit_Briefs
16	2231	2222	Prevail_Breezers_Adult_Briefs
17	2230	2222	Prevail_Breezers_Adult_Briefs
18	2229	2222	Prevail_Breezers_Adult_Briefs
19	2228	2222	Prevail_Breezers_Adult_Briefs
20	2227	2222	Prevail_Breezers_Adult_Briefs	Prevail Breezers Adult Briefs	TRUE
21	2226	2222	Prevail_Breezers_Adult_Briefs
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1	Cell Value	duplicates	text	NO

and this is what I'm trying to achieve.

Incontinence Category_UPDATED2022_TP.xlsx
	A	B	C	D	E
1	Variant Metafield: Unique-Internal-ID.Unique_Internal_ID [string]	Variant Metafield: Product-Family-ID.Product_Family_ID [string]	Handle	Title	Top Row
2	82650	82644	Attends_Extended_Wear_Brief	Attends Extended Wear Brief	TRUE
3	82649	82644	Attends_Extended_Wear_Brief
4	82648	82644	Attends_Extended_Wear_Brief
5	82647	82644	Attends_Extended_Wear_Brief
6	82646	82644	Attends_Extended_Wear_Brief
7	82645	82644	Attends_Extended_Wear_Brief
8	1659	1655	Cardinal_Protective_Underwear	Cardinal Protective Underwear	TRUE
9	1658	1655	Cardinal_Protective_Underwear
10	1656	1655	Cardinal_Protective_Underwear
11	2216	2207	Nu-Fit_Briefs	Nu-Fit Briefs	TRUE
12	2215	2207	Nu-Fit_Briefs
13	2213	2207	Nu-Fit_Briefs
14	2212	2207	Nu-Fit_Briefs
15	2211	2207	Nu-Fit_Briefs
16	2231	2222	Prevail_Breezers_Adult_Briefs	Prevail Breezers Adult Briefs	TRUE
17	2230	2222	Prevail_Breezers_Adult_Briefs
18	2229	2222	Prevail_Breezers_Adult_Briefs
19	2228	2222	Prevail_Breezers_Adult_Briefs
20	2227	2222	Prevail_Breezers_Adult_Briefs
21	2226	2222	Prevail_Breezers_Adult_Briefs
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1	Cell Value	duplicates	text	NO

Again I've tried using filters and custom sort but I'm not getting anywhere
As long as the title appears in the first row then It will work, but as you can see the titles are appearing further down.

Please can someone help me out please?

 

[Peter_SSs]

See if this does what you want. Test with a copy of your data as it over-writes the existing values in columns D:E.

Sub MoveToTop()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  a = Range("C1", Range("C" & Rows.Count).End(xlUp)).Resize(, 3).Value
  ReDim b(1 To UBound(a) - 1, 1 To 2)
  For i = UBound(a) To 2 Step -1
    If Len(a(i, 2)) > 0 Then k = i
    If a(i, 1) <> a(i - 1, 1) Then b(i - 1, 1) = a(k, 2): b(i - 1, 2) = a(k, 3)
  Next i
  Range("D2:E2").Resize(UBound(b)).Value = b
End Sub

 

[desibouy]

See if this does what you want. Test with a copy of your data as it over-writes the existing values in columns D:E.

Sub MoveToTop()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  a = Range("C1", Range("C" & Rows.Count).End(xlUp)).Resize(, 3).Value
  ReDim b(1 To UBound(a) - 1, 1 To 2)
  For i = UBound(a) To 2 Step -1
    If Len(a(i, 2)) > 0 Then k = i
    If a(i, 1) <> a(i - 1, 1) Then b(i - 1, 1) = a(k, 2): b(i - 1, 2) = a(k, 3)
  Next i
  Range("D2:E2").Resize(UBound(b)).Value = b
End Sub

Hi Peter,

Thanks for reply.

I've just tried this but it doesn't work. I'm getting 'Mismatch' error. This code being highlighted Yellow.

 If a(i, 1) <> a(i - 1, 1) Then

Just to clarify, you said it will replace Columns D:E, however I don't usually use Column E (Top Row) I've left it in my example as a way to distinguish how it should be.
I'll try and explain again, probably my fault but the variants for some reason appear further down the file. Shopify uses Handle and Title to add, replace and merge other columns. So let's say I want to update the products price, I need Handle and Title in all my spreadsheets but because I've filtered over and over again either to sort out categories, products names, and other such thing, my products are not in any order so when I import, Shopify says wait, the same handle or title appears further down the sheet. So this what I'm after, a way to regroup those products no matter how many times I change the sorting. in our old system, we used Unique ID and Product Family ID, I've been trying to use this to, to help with sorting.

I hope that clarifies. Sorry for the confusion.

 

[desibouy]

I found another solution. But thank you Peter.

 

[Peter_SSs]

Glad you have it resolved. Thanks for letting us know.