• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return header value

rrocker1405

rrocker1405

Member
Hi team,

I'm trying to retrieve header information with list of all unique values in columns from different worksheets. Attached is a sample sheet with the desired output, i'm not sure if index and match can be used to create as listed in the output sheet.

In the output sheet lists the following information in respective columns
  • Country (common field data across sheets, always)
  • Unique values is the data from Column A from the 3 worksheets
  • Header information includes the list of all headers where Column A values are listed e.g., ABC in sheet 1 has header of Column 1, STU in Sheet 2 has header of header 1

any suggestions and help would be helpful.

Kind regards,
A!
 

Attachments

  • Data.xlsx
    10.9 KB · Views: 16
This is quite interesting, can you please share the correlation between all sheets.

suggestion-
Yes, this is possible thru index / match but for range we have to use INDIRECT function to make dynamic range.


rrocker1405 said:
Hi team,

I'm trying to retrieve header information with list of all unique values in columns from different worksheets. Attached is a sample sheet with the desired output, i'm not sure if index and match can be used to create as listed in the output sheet.

In the output sheet lists the following information in respective columns
  • Country (common field data across sheets, always)
  • Unique values is the data from Column A from the 3 worksheets
  • Header information includes the list of all headers where Column A values are listed e.g., ABC in sheet 1 has header of Column 1, STU in Sheet 2 has header of header 1
any suggestions and help would be helpful.

Kind regards,
A!
Click to expand...
 
Hi David,

Columns A through D are more like transactional data where the data keeps updating.
The 3 worksheets are basically different sources put together in a spreadsheet.
The Columns E and F are the binding factor for all the data they will remain static (e.g., no other options other than Country 1 or country 2)

Please let me know if you would need more information / clarifications. Thanks in advance.

kind regards,
A!
 
bines53 said:
Hi @rrocker1405,

With built-in Excel functions, it is very difficult to impossible, you need to download a plugin here ,http://www.businessfunctions.com/index.php?pageno=65,
bff.exe, or bfx.exe
To use the function =JoinLists,
David
Click to expand...

It is possible to use Excel functions to extract unique data from Column A cross 3 worksheets.

1] In F2, array formula ( confirm entered with SHIFT+CTRL+ENTER) copy down :

=INDIRECT("sheet"&TEXT(MIN(IF(COUNTIF($F$1:F1,T(INDIRECT("Sheet"&{1,2,3}&"!A"&ROW($2:$100)))),39999,{1,2,3}/1%%+ROW($2:$100))),"0\!A0000"))&""

2] Attached file in compare with the above array formula and the JoinLists formula.

Regards
Bosco
 

Attachments

  • Data-3A.xlsx
    12.3 KB · Views: 5
Hi Bosco ,

You forgot to say, its solution is based on a volatile function (INDIRECT).

I was weaned volatile functions, many years ago, creating chaos, you have specified this in one of the last threads.
Should be avoided, at least there are plug-ins written in C language.


Regards
David
 
If there are no external references to be looked at then INDIRECT can be used safely. INDIRECT doesn't work with closed workbook references is one condition that the user must be aware of besides volatility.

Volatile formulas will always calculate and will give calculation overhead but shouldn't matter if the sheet is not slowing down too much.
 
bines53 said:
Hi Bosco ,
You forgot to say, its solution is based on a volatile function (INDIRECT).
I was weaned volatile functions, many years ago, creating chaos, you have specified this in one of the last threads.
Should be avoided, at least there are plug-ins written in C language.
Regards
David
Click to expand...

Hi David,

Replace INDIRECT with other non-volatile function will make the formula very long.

Here is the replacement.

In E2, formula copy down :

=IFERROR(INDEX(Sheet1!A$2:A$5,MATCH(0,INDEX(COUNTIF(E$1:E1,Sheet1!A$2:A$5&""),),0)),IFERROR(INDEX(Sheet2!A$2:A$4,MATCH(0,INDEX(COUNTIF(E$1:E1,Sheet2!A$2:A$4&""),),0)),IFERROR(INDEX(Sheet3!A$2:A$4,MATCH(0,INDEX(COUNTIF(E$1:E1,Sheet3!A$2:A$4&""),),0)),"")))

Regards
Bosco
 
Thank you Bosco, David for helping me out here. I will see what needs to be done here to have the output as desired.

Thank you once again...A!

bosco_yip said:
Hi David,

Replace INDIRECT with other non-volatile function will make the formula very long.

Here is the replacement.

In E2, formula copy down :

=IFERROR(INDEX(Sheet1!A$2:A$5,MATCH(0,INDEX(COUNTIF(E$1:E1,Sheet1!A$2:A$5&""),),0)),IFERROR(INDEX(Sheet2!A$2:A$4,MATCH(0,INDEX(COUNTIF(E$1:E1,Sheet2!A$2:A$4&""),),0)),IFERROR(INDEX(Sheet3!A$2:A$4,MATCH(0,INDEX(COUNTIF(E$1:E1,Sheet3!A$2:A$4&""),),0)),"")))

Regards
Bosco
Click to expand...
 
Hi Bosco ,

There is no doubt volatile functions are often shorter, and that's their only advantage, when there are better alternatives, plug-ins(add in) written in C language. which also lacks many functions in Excel as JoinLists.

There are only SIX functions in Excel irreplaceable,MODE.MULT ,MATCH,COUNTIFS,FREQUENCY,SUMIFS,AGGREGATE.

Regards
David
 
Hi David and Bosco,

I found a easier solution with PowerQuery by creating a duplicate copy of certain fields and later unpivoting other columns to arrive at a solution. Thank you once again for looking up for me on the same.

Kind regards,
A!
 
You must log in or register to reply here.
Back
Top