How to use IFERROR, INDEX, MATCH if the amount of column is limited

[gargilang]

Hello, I would like to know if it would be possible to use the IFERROR, INDEX, MATCH function on below scenario.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Country	City	Image	Country	City1	City2	City3	Image1	Image2	Image3
2	France	Paris	Paris_France_xxx.jpg	France	Paris			Paris_France_xxx.jpg
3	Canada	Toronto	Toronto_Canada_xxx.jpg	Canada	Toronto	Montreal		Toronto_Canada_xxx.jpg	Montreal_Canada_yyy.jpg
4	Canada	Montreal	Montreal_Canada_yyy.jpg	USA	Chicago	Boston	Miami	Chicago_USA_xxx.jpg	Boston_USA_yyy.jpg	Miami_USA_zzz.jpg
5	USA	Chicago	Chicago_USA_xxx.jpg	UK	London	Liverpool	Manchester	London_UK_xxx.jpg	Liverpool_UK_yyy.jpg	Manchester_UK_zzz.jpg
6	USA	Boston	Boston_USA_yyy.jpg	UK	Cambridge			Cambridge_UK_aaaa.jpg
7	USA	Miami	Miami_USA_zzz.jpg	Japan	Tokyo	Kyoto	Osaka	Tokyo_Japan_xxx.jpg	Kyoto_Japan_yyy.jpg	Osaka_Japan_zzz.jpg
8	UK	London	London_UK_xxx.jpg	Japan	Yokohama	Nagoya		Yokohama_Japan_aaaa.jpg	Nagoya_Japan_bbbb.jpg
9	UK	Liverpool	Liverpool_UK_yyy.jpg
10	UK	Manchester	Manchester_UK_zzz.jpg
11	UK	Cambridge	Cambridge_UK_aaaa.jpg
12	Japan	Tokyo	Tokyo_Japan_xxx.jpg
13	Japan	Kyoto	Kyoto_Japan_yyy.jpg
14	Japan	Osaka	Osaka_Japan_zzz.jpg
15	Japan	Yokohama	Yokohama_Japan_aaaa.jpg
16	Japan	Nagoya	Nagoya_Japan_bbbb.jpg
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	=INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0))
E2:G5,E7:G7	E2	=IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), "")
H2:H5,H7	H2	=IFERROR(INDEX($C$2:$C$16, MATCH(0, COUNTIF($D2:D2,$C$2:$C$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), "")
I2:J5,I7:J7	I2	=IFERROR(INDEX($C$2:$C$16, MATCH(0, COUNTIF($D2:H2,$C$2:$C$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), "")
D7	D7	=INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D5, $A$2:$A$16), 0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Based on the data of Countries and Cities filled in yellow on the left, by using the IFERROR, INDEX, MATCH formula I managed to get all the data I need. But since I limited the amount of column into 3, what if there are more than 3 City. What I want is for the excel to continue the list of cities by creating another row under it as example of row filled in red.

I hope it makes sence. Let me know if it's possible.

 

[Tom Urtis]

Formulas on their own do not "create another row". There is probably some obnoxious formula solution that would solve this but, and my bias is admittedly showing but I suggest, a VBA solution would be much more robust and simpler.

 

[gargilang]

Formulas on their own do not "create another row". There is probably some obnoxious formula solution that would solve this but, and my bias is admittedly showing but I suggest, a VBA solution would be much more robust and simpler.

hi Tom, thank you so much for replying. i am such a newbie with excel that even the formula that i use here i found online after 3 days of keep changing search keyword. If it not too much to ask. Could you show me the VBA solution that would achieve what I need?

 

[Tom Urtis]

More info is needed to describe how the data in columns A, B, and C get there.

Are they fomulas, or manually entered, or imported from an external list, or copied and pasted from elsewhere in the workbook,
and
what is it that triggers the production of data in D:J.
and
is it updated manually or daily or weekly or every full moon or whatever.

 

[gargilang]

More info is needed to describe how the data in columns A, B, and C get there.

Are they fomulas, or manually entered, or imported from an external list, or copied and pasted from elsewhere in the workbook,
and
what is it that triggers the production of data in D:J.
and
is it updated manually or daily or weekly or every full moon or whatever.

Hi Tom, Thank you so much for your interest to answer. However I ended up asking a formula to make the Country data become unique within itself.
So based on the above table, for example Country UK will be UK1, UK1, UK1, UK2 and Country Japan will be Japan1, Japan1, Japan1, Japan2 and Japan2.
It should look like below:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Country	City	Image	Country	City1	City2	City3	Image1	Image2	Image3
2	France	Paris	Paris_France_xxx.jpg	France	Paris			Paris_France_xxx.jpg
3	Canada	Toronto	Toronto_Canada_xxx.jpg	Canada	Toronto	Montreal		Toronto_Canada_xxx.jpg	Montreal_Canada_yyy.jpg
4	Canada	Montreal	Montreal_Canada_yyy.jpg	USA	Chicago	Boston	Miami	Chicago_USA_xxx.jpg	Boston_USA_yyy.jpg	Miami_USA_zzz.jpg
5	USA	Chicago	Chicago_USA_xxx.jpg	UK1	London	Liverpool	Manchester	London_UK_xxx.jpg	Liverpool_UK_yyy.jpg	Manchester_UK_zzz.jpg
6	USA	Boston	Boston_USA_yyy.jpg	UK2	Cambridge			Cambridge_UK_aaaa.jpg
7	USA	Miami	Miami_USA_zzz.jpg	Japan1	Tokyo	Kyoto	Osaka	Tokyo_Japan_xxx.jpg	Kyoto_Japan_yyy.jpg	Osaka_Japan_zzz.jpg
8	UK1	London	London_UK_xxx.jpg	Japan2	Yokohama	Nagoya		Yokohama_Japan_aaaa.jpg	Nagoya_Japan_bbbb.jpg
9	UK1	Liverpool	Liverpool_UK_yyy.jpg
10	UK1	Manchester	Manchester_UK_zzz.jpg
11	UK2	Cambridge	Cambridge_UK_aaaa.jpg
12	Japan1	Tokyo	Tokyo_Japan_xxx.jpg
13	Japan1	Kyoto	Kyoto_Japan_yyy.jpg
14	Japan1	Osaka	Osaka_Japan_zzz.jpg
15	Japan2	Yokohama	Yokohama_Japan_aaaa.jpg
16	Japan2	Nagoya	Nagoya_Japan_bbbb.jpg
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0))
E2:G8	E2	=IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), "")
H2:H8	H2	=IFERROR(INDEX($C$2:$C$16, MATCH(0, COUNTIF($D2:D2,$C$2:$C$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), "")
I2:J8	I2	=IFERROR(INDEX($C$2:$C$16, MATCH(0, COUNTIF($D2:H2,$C$2:$C$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), "")

 

[gargilang]

Someone answered the same question I posted here. Please see below is the VBA code to perform above task I intended.

Sub condense()
    Dim src, dest(), ws As Worksheet, srcRange As Range, i As Long, j As Long, countryCount As Long, rowNum As Long
    Set ws = ActiveSheet
    Set srcRange = ws.Cells(1, 1).Resize(ws.Cells(ws.Rows.Count, 1).End(xlUp).Row, 3)
    src = srcRange.Value2
    ReDim dest(1 To UBound(src, 1) - 1, 1 To 7)
    rowNum = 1
    i = 2
    Do While i <= UBound(src, 1)
        countryCount = Application.CountIf(srcRange.Columns(1), src(i, 1))
        For j = 1 To countryCount
            dest(rowNum + Int((j - 1) / 3), 1) = src(i + j - 1, 1)
            dest(rowNum + Int((j - 1) / 3), 2 + ((j - 1) Mod 3)) = src(i + j - 1, 2)
            dest(rowNum + Int((j - 1) / 3), 5 + ((j - 1) Mod 3)) = src(i + j - 1, 3)
        Next j
        i = i + countryCount
        rowNum = rowNum + 1 + Int((countryCount - 1) / 3)
    Loop
    ws.Cells(2, 4).Resize(rowNum, 7).Value2 = dest
    With ws.Cells(1, 4).Resize(1, 7)
        .Value2 = Strings.Split("Country,City1,City2,City3,Image1,Image2,Image3", ",")
        .EntireColumn.AutoFit
    End With
End Sub

 

[Fluff]

Someone answered the same question I posted here.

I future please post any cross-post links at the time, not 8 days later.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!