Copy rows

[Cadmium]

Hi guys,

First of all, I would like to say that I've done some research before posting here. I know similar questions have already been asked, but I just couldn't get every thing to work.

So I'm doing a project on a survey for my statistics classes, for which I received a few spreadsheet to work with. I have this "individu" spreadsheet that contains 14045 entries (one for each person interviewed) and this "kish" spreadsheet that contains 5799 entries (one for each person from the "individu" sheet that responded to some extra questions in the survey). Problem is that I'm mostly interested in the answers displayed in the "kish" spreadsheet, but I want to be able to use the data that is displayed in the "individu" spreadsheet.

What I want to do is to copy complete rows from the "individu" spreadsheet to the "kish" spreadsheet that obey to two rules:

- the "IDENT" value of the row in "individu" must match the "IDENT" value of the corresponding row in "kish"

- the "NO" value of the row in "individu" must match the "NO" value of the corresponding row in "kish"

What should I do?

Thanks in advance!

OBS: tried to upload the excel file, but it exceeds 7 MB and I keep getting an error when trying to upload it in a zip file, so I uploaded screenshots from each of the spreadsheets!

[ConneXionLost]

Hi Cadmium,

Assuming the "entries" are one row each, and the "IDENT" is a unique key to match individuals to the survey responses, you could use the INDEX and MATCH functions to bring your data from "individu" to "kish". Then use the filter to show only the "NO" responses.

If you attach a sample spreadsheet, then myself or someone else here in the forum should be able to guide you through the INDEX & MATCH functions.

Cheers,

[ConneXionLost]

Hi Cadmium,

Okay, so somewhere out on the first row of "kish" (beyond your current data, paste this in an empty cell:

Please Login or Register  to view this content.

Adjust the ranges as required (I can't see how large your dataset is) then autofill it out to the right & down.

Cheers,

[Cadmium]

Hi, thanks for answering!

The problem with what you said is that IDENT is not a unique key, the combination IDENT and NO is (IDENT is a code for a house, and NO is a code for each person living in the house that was interviewed). That is way I need to look for similar IDENT and NO values, since only one person per house answered to the extra questions (the kish questions).

I don't know why, but when I type in the code you wrote, Excel tells me there is an error!

Is there any way of doing this using Macro (I'm asking because almost all solutions I found while searching involved Macros)?

I've attached a sample spreadsheet with 30 entries in "individu" that correspond to 20 entries in "kish".

Thanks!

[ConneXionLost]

In that case you could add a helper column on "individu" out beyond the data with this:

Please Login or Register  to view this content.

then refer to that column in the "kish" formula:

Please Login or Register  to view this content.

in the red hi-lited portion.

Note - I removed the IF function.

[Cadmium]

That worked!

Well, almost. I was able to copy the correct data from columns IDENT to NIVETUD1, but from columns NIVETUD2 to NATIO7 all I saw was errors due to bad references.

Thanks for the help until now!

[ConneXionLost]

Just a small modification to the formula to account for all the columns you are using:

Please Login or Register  to view this content.

Cheers,

[ConneXionLost]

Bumped due to edit.

[Cadmium]

That was so silly of me, sorry... Thanks for the help.

Just one last question, not really related to the topic, but it has been annoying me: is there any simple way of converting the data in the spreadsheet to numbers? Many of the data I have in the spreadsheet was stored in the form of text, so when I try to do mathematical operations I get a wrong result.

Thanks!

[ConneXionLost]

Hi Cadmium,

Sure. In an empty cell (formatted as General) enter a "1" (no quotes).

Copy the cell.

High-lite the cells (with numbers behaving as text) you want to convert, and select:

Edit > Paste Special > Multiply > OK

(Sorry, I don't know the sequence for Excel 2007.)

Cheers,

P.S. - If this is all you need, please mark the thread [SOLVED]

[Cadmium]

That worked just fine! Thanks for all the help provided, ConneXionLost!