Search and return data from a row in one sheet to every other row in a column in another

[Seeki]

I have data in a table in sheet2, and I have a field on sheet 1 to enter the Record Id as a search criteria, and based on the Type associated with the record id used in the search I want to to find and return specific colums from the associated row in sheet 2 to sheet1 column c every other row. For example, lets say I have a table like the below (actual has around 40 columns) on sheet 2:

RECORDID TYPE COAT COLOR TEMPERMENT FOOD
12345 Bird Feather Blue Fun Seeds
12346 Cat fur Tiger Grouchy Chicken
12347 Dog fur Black Friendly Beef
12348 Fish Scale Gold Fun Flakes

If I enter 12345 I want to return data from that row to sheet1 as follows:
cell c3 = 12345
cell c5 = Bird
cell c7 = Feather
cell c9 = Blue
cell c11 =
cell c13 = Seed

If I enter 12345 I want to return data from that row to sheet1 as follows:
cell c3 = 12345
cell c5 = Cat
cell c7 =
cell c9 = Tiger
cell c11 =
cell c13 =

Where there is nothing after the equal means that information should not be carried over to sheet1; i.e. for record 12345 the temperment (fun) should not be carried over to sheet1, and for record 12346 the coat (fur), temperment (groughy) and food (chicken) should not be carried over to sheet1.
As indicated, the actual sheet has about 50 columns in sheet2 and only values from specific columns based on the TYPE column for the selected record id should be brought over to sheet1.

The number of records on sheet2 is dynamic and will change.

Any ideas on the best way to do this

Thanks Seeki

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[David A Coop]

Hi Seeki,

You really didn't supply enough information, as I have no idea what is on the empty alternate rows. I have assumed they are blank.

I have also assumed that you have the HEADINGS from Sheet 2 in Column B on Sheet 1.

In the attached,I have used a Named Range (dRange) for dynamic range as follows: =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$1:$1))

Then in column C, the formula =IF(VLOOKUP($C$1,dRange,COUNTA($B$3:B3),0)=0,"",VLOOKUP($C$1,dRange,COUNTA($B$3:B3),0)) looks up the appropriate values.

Best I can do with the information provided. If you want more, please post a sample workbook!

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

[bakerman2]

Make use of excel built-in features and make it a real ExcelTable (Ribbon: Insert - Table).

And why not use IFERROR instead of double statement in formula ?

[David A Coop]

And why not use IFERROR instead of double statement in formula ?

Thought of that, but I didn't get an error. The vlookup returned a ZERO. Seeki wanted the cell to be blank.

DAC

[Seeki]

Thanks again for replying. The below is the VBA code that I am using to get what I needed. I was able to create it after watching a few youtube videos by Dinesh Kumar Takyar. None specifically address what I needed but I was able to tweak the information from his videos to meet my need.

Please Login or Register  to view this content.

[David A Coop]

Hi Seeki,

Are you saying that the code you posted has resolved your problem, or are you asking about the code? It could certainly be made a LOT shorter!

Regards,

David

[Seeki]

Hi David,

Thanks for following up. The code I posted IS working for me, so yes it has resolved my problem. However, since you indicated it could be a lot shorter, I would be interested in knowing how to make it shorter.

Basically I created a form using cells in excel (not userform) by locking all cells except the ones I am using for data entry. Some of the data entry cells are conditionally locked and gray to limit entry of data based on value user selected in the very first cell in the form. The information entered in the form is saved to another sheet in the workbook. This code is to pull back a record from the saved information based on a criteria entered in cell H3 of the data entry form. Hence the multiple segments in my code. If it can be made shorter I am definitely interested in knowing how.

[David A Coop]

Hi Seeki,

I can't test this as I don't have a workbook, but here is a small slice of your code to give you an idea.

1. I have declared two worksheets (ws1 and ws2) and Set them to be equal to Sheets1 and 2. From then on, you can refer to them as ws1 and ws2 instead of Sheets("sheet1"). Saves a lot of time.
2. Instead of assigning a value to every cell, I used a loop. The macro just cycles around 19 timed, incrementing the address by 1 or 2.

NOTE: This is not complete - just to give you an idea if how to create more efficient code. Have a go, and if you get into difficulty, post a copy of your workbook (or a desensitized sample).

Please Login or Register  to view this content.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

[Seeki]

Hi David,
Tested it and it works. Thanks, much appreciated!