array formula to provide all values associated with a given value
I should mention that I'd like the output from the table below to look
something like this: 1000020 Td;Tz;Ug;Zm;Zw 1000035 Ao;Mz etc. Thanks - Toby "tschaeffer" wrote in message news:... From an Access Query. I've got a list with the unique ID's for reports, and the next field indicates what Language they were written in. This was from a Many - Many relationship in Excel, and many of the reports were written in many different languages. ReportID CountryID 1000020 Td 1000020 Tz 1000020 Ug 1000020 Zm 1000020 Zw 1000035 Ao 1000035 Mz I've got over 10,000 records like this and would like an array formula that for each Report ID (and i've got a separate list of those of course) provides a listing (preferably tab or semicolon delimited) of every single country that that report covers. Any suggestions? Thanks - Toby |
array formula to provide all values associated with a given va
In Sheet 2:
A1: =IF(ISERR(SMALL(IF(MATCH(ReportID,ReportID,0)=ROW( INDIRECT("1:"&ROWS(ReportID))),MATCH(ReportID,Repo rtID,0)),ROWS($1:1))),"",INDEX(ReportID,SMALL(IF(M ATCH(ReportID,ReportID,0)=ROW(INDIRECT("1:"&ROWS(R eportID))),MATCH(ReportID,ReportID,0)),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy down as far as needed B1: =IF(ISERR(SMALL(IF(ReportID=$A1,ROW(INDIRECT("1:"& ROWS(CountryID)))),COLUMNS($B:B))),"",INDEX(Countr yID,SMALL(IF(ReportID=$A1,ROW(INDIRECT("1:"&ROWS(C ountryID)))),COLUMNS($B:B)))) ctrl+shift+enter, not just enter copy across and down as far as needed "tschaeffer" wrote: I should mention that I'd like the output from the table below to look something like this: 1000020 Td;Tz;Ug;Zm;Zw 1000035 Ao;Mz etc. Thanks - Toby "tschaeffer" wrote in message news:... From an Access Query. I've got a list with the unique ID's for reports, and the next field indicates what Language they were written in. This was from a Many - Many relationship in Excel, and many of the reports were written in many different languages. ReportID CountryID 1000020 Td 1000020 Tz 1000020 Ug 1000020 Zm 1000020 Zw 1000035 Ao 1000035 Mz I've got over 10,000 records like this and would like an array formula that for each Report ID (and i've got a separate list of those of course) provides a listing (preferably tab or semicolon delimited) of every single country that that report covers. Any suggestions? Thanks - Toby |
array formula to provide all values associated with a given value
Not (practically) possible using built-in functions.
One possibility is to download the *free* add-in Morefunc.xll which has a function (MCONCAT) that *might* do what you want. http://xcell05.free.fr/english/ Then you could use an array formula like this: =SUBSTITUTE(TRIM(MCONCAT(IF(J$1:J$5=A1,K$1:K$5,"") &" "))," ",";") Whe A1 = a report number J1:J5 = range of repot numbers K1:K5 = range of languages With 10K rows of data *expect* this to be slow! -- Biff Microsoft Excel MVP "tschaeffer" wrote in message ... I should mention that I'd like the output from the table below to look something like this: 1000020 Td;Tz;Ug;Zm;Zw 1000035 Ao;Mz etc. Thanks - Toby "tschaeffer" wrote in message news:... From an Access Query. I've got a list with the unique ID's for reports, and the next field indicates what Language they were written in. This was from a Many - Many relationship in Excel, and many of the reports were written in many different languages. ReportID CountryID 1000020 Td 1000020 Tz 1000020 Ug 1000020 Zm 1000020 Zw 1000035 Ao 1000035 Mz I've got over 10,000 records like this and would like an array formula that for each Report ID (and i've got a separate list of those of course) provides a listing (preferably tab or semicolon delimited) of every single country that that report covers. Any suggestions? Thanks - Toby |
All times are GMT +1. The time now is 09:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com