Like Vlookup but different...

[Tom.Jones]

Howdy

I need a little help.
I receive from an application, an excel file, which has the following structure:
a table (not a real table) with 4 columns and a variable number of rows. Next is an empty column and then another table with 4 columns, and so on. (So far I haven't received more than 21 tables.)
The table header is the same for all tables in the sheet.
Every day we receive this file which does not have a fixed number of tables. Today it can have 9 tables and tomorrow it can have 15, the next day it can have 7 tables.
So in row 1 starting with A1 there are 4 columns with different headers, (but the headers are the same for each table) followed by an empty column and then the next table, and so on.
I would like in another sheet, to be able to write, any item, (or to put a data validation with all the items from the first column of each table) that is in the first column of each table and in the next cell to appear my correspondent from the same row, of the searched item, in column 4 of the table in which the searched item is located.
The data in the first column is unique.
I would like, if it is possible to solve it with formulas, or with VBA code if it is not possible with formulas.
Thanks.

 

[EFANYoutube]

Hi Tom.Jones
I understand the sheet and the "Tables" I don't inderstand this bit:

"I would like in another sheet, to be able to write, any item, (or to put a data validation with all the items from the first column of each table) that is in the first column of each table and in the next cell to appear my correspondent from the same row, of the searched item, in column 4 of the table in which the searched item is located."

As far as I can understand, you want a new sheet to show all the items from Column A and then beside them show all the cell addresses they show up in the other tables?

 

[Tom.Jones]

As far as I can understand, you want a new sheet to show all the items from Column A and then beside them show all the cell addresses they show up in the other tables?

No.
Let say, in sheet1 cell B2 i will write an item from one of column (first column in one 'table') and in C2 I want to get the words in last column (same row with item I look.
Or create a DV in B2 and in the list of DV to have all items in first column of each 'table'.
Say in B2 sheet1 I choose (or write) Item4 and want in C2 to appear 'af'
If in B2 sheet1 I choose (or write) Item13 and want in C2 to appear 'wrr'

In sheet2 data is like this:

H1

H2

H3

H4

H1

H2

H3

H4

H1

H2

H3

H4

Item1

aa

ss

rqrwef

Item12

XX

RR

ddd af

item17

a

s

ada

Item2

aa

ss

mmm ffda

Item13

XX

RR

wrr

item18

d

v

thhdh

Item3

aa

ss

wqqwe

Item14

XX

RR

wrq ftjhyt

item19

f

g

sf

Item4

aa

ss

af

Item15

XX

RR

wqr dhe

item20

s

d

dgfd dgd

Item5

aa

ss

hftu jrssy

Item16

XX

RR

EQW

item21

a

d

erwet

Item6

aa

ss

srs sgrs gg

item22

a

w

ds dh th

Item7

aa

ss

etwe

item23

r

l

dfsgs

Item8

aa

ss

dfs dhth

Item9

aa

ss

erqre gg

Item10

aa

ss

aafg

Item11

aa

ss

fewe swt

 

[Fluff]

One option for 365.
Out this formula in (for instance) A2

=LET(rng,Sheet2!A2:DA100,r,ROWS(rng),s,SEQUENCE(r*COLUMNS(rng)/5,,0),a,INDEX(rng,MOD(s,r)+1,INT(s/r)*5+{1,4}),FILTER(a,INDEX(a,,1)<>""))

then in your data validation you can use

=INDEX(A2#,,1)

You can the use a vlookup to get the relevant value.

 

[Tom.Jones]

Fluff,

Excellent, excellent job.
I'm curious what a VBA code would look like.
If you have time, I would ask you to write a VBA code.
Anyway, thank you very much.

 

[Fluff]

Glad we could help & thanks for the feedback.
I'll have a look at a macro if I get time.

 

[EFANYoutube]

This code would get you what you want, gust need to call it with a button or a Worksheet Change event

Sub FindValue()
Sheet1.Range("C2").Value = Sheet2.Cells.Find(what:="item1", lookat:=xlWhole).Offset(0, 3).Value
End Sub

 

[Tom.Jones]

@EFANYoutube,

Excellent. Thank you.