Tags: REPT

Convert a text entry into its number equivalent

{ 0 Comments }

Consider this simple two column table showing text entries in column A and the corresponding numbers in column.  Assume this data is in range A2:B11 (headings are in A1:B1). text Value A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 0 The objective is to […]

Read More →

Summarise data from multiple sheets with one condition – PartII

{ 2 Comments }

Assume a worksheet which has scores (1-10) on four future dates on nine questions.  Each row represents responses for one question.  Likewise there are 20 respondents i.e. 20 worksheets which have the same structure. One may want to summarise data from all 20 worksheets into one worksheet.  The question and solution are more clearly explained in the attached […]

Read More →

Extract data from unknown lookup range

{ 14 Comments }

The VLOOKUP() function accepts a definite lookup array (second input in the VLOOKUP()) specified by the end-user.  In a situation where the lookup array is unknown, a function may have to cycle through various lookup ranges to cull out the required value. You may refer to my solution in this workbook.

Read More →